Mod python - mysql lock

[Jan Danielsson]

Roopesh wrote:
> 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.

-- 
Kind regards,
Jan Danielsson