Yes. What would happen if 2 processes running this code executed the query at about the same time? They would both receive the same value for MIN(id). Locking the row would guarantee only one user would get the MIN(id); the 2nd (and subsequent users) would wait until the lock is released.
----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 15, 2004 5:20 AM To: [EMAIL PROTECTED] Subject: Re: SQL question: Find next unused number... > From: Steve Baldwin [mailto:[EMAIL PROTECTED] > > LOCK TABLE user_table IN EXCLUSIVE MODE ; > SELECT MIN ... > INSERT INTO user_table ... > COMMIT ; On 2004-09-14 14:53:06 -0600, Reidy, Ron wrote: > Or ... > > SELECT MIN(id) > FROM t > FOR UPDATE; > > Would only cause a row lock. Does that help in this case? The locked row isn't going to be updated, but a new one is being inserted. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED] | -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.