Andy Sy a écrit :
>
> 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)
>
Hi,
If you use LAST_INSERT_ID(), you don't need to use LOCK:
UPDATE TBL SET COL=LAST_INSERT_ID(COL+1);
select LAST_INSERT_ID();
Hope this helps
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
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
