Mod python - mysql lock

[Roopesh]

Hi,
In my mod_python project I am using mysql as the database. There is
table card in which unique cards are stored. When a user request comes
he has to get a unique card. In this situation I want to use LOCK with
which I can prevent other users accessing the table. I tried excuting
"LOCK" command of mysql through python code, but it is not locking the
database. Any ideas why this isn't working and how can I do the same.

//python code
sql = "LOCK TABLES card WRITE"
cursor.execute(sql)


Regards
Roopesh

 

[Jan Danielsson]

In my mod_python project I am using mysql as the database. There is
table card in which unique cards are stored. When a user request comes
he has to get a unique card. In this situation I want to use LOCK with
which I can prevent other users accessing the table. I tried excuting
"LOCK" command of mysql through python code, but it is not locking the
database. Any ideas why this isn't working and how can I do the same.

//python code
sql = "LOCK TABLES card WRITE"
cursor.execute(sql)

How have you verified that the lock isn't actually working?

From http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html:

"UNLOCK TABLES explicitly releases any locks held by the current
thread. All tables that are locked by the current thread are implicitly
unlocked when the thread issues another LOCK TABLES, or when the
connection to the server is closed."

Note the last part -- the lock is apparently released when the
connection is closed. Is that perhaps what is happening? Remember that
once your request is done in mod_python, it will (probably) release any
allocated resources, which means that your connection will be closed,
and your lock released, if it wasn't previously.

Database locks are, generally, bad. Avoid them, and only use them
when they really are the only solution.

From what little information you supply, it seems that what you need
is simply automatic id generation. Essentially you to do this:

INSERT INTO foobar (title) VALUES ('yuck')

SELECT LAST_INSERTED_ID()

foobar needs to have an auto_increment column, and LAST_INSERTED_ID()
is probably called something else (long time since I used MySQL).

This is actually more of a MySQL question. Or even a generic database
design question. I would suggest you try asking in a more appropriate forum.

 

[John Nagle]

If you use MySQL 5, you get atomic transactions. The old
"LOCK" thing is becoming obsolete. Suggest getting a newer
MySQL and a newer MySQL book.

John Nagle