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?
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
