-----Original Message----- From: Andy Sy [mailto:an...@netfxph.com] Sent: Wednesday, June 12, 2002 12:03 PM To: mys...@lists.mysql.com Subject: Auto-increment across multiple tables / Atomicity of update statements

In MySQL,

I want to use an auto-incrementing value that works across multiple tables. For example I have 3 tables forumA, forumB, forumC, each of which has a primary key field called 'msgnum'. However, if I declare 'msgnum' auto_increment, 'msgnum' will not be unique across the 3 tables.

Thus, instead of using an auto_increment column, I made an auxiliary table which will hold the last used 'msgnum' and update it each time a new record is inserted in any of the 3 tables. Since I plan to use this auxiliary table to hold other values as well and INSERTs to the 3 tables may happen extremely often, I would rather not have the overhead of repeatedly LOCKing and UNLOCKing the table.

In connection with this, the following 2 issues crop up:

#1) Is the following statement guaranteed atomic?

UPDATE TBL SET COL=COL+1

and is there anyway to retrieve the value of COL that was last set by the connection that set it?

#2) If a thread with a LOCK on a table unexpectedly dies without being able to UNLOCK it, does it automatically relinquish the lock?

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-r9374c=moto...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php