Yves Goergen wrote:
On 13.11.2007 16:37 CE(S)T, mark addison wrote:
As your using InnoDB, which has row level locking a SELECT ... FOR
UPDATE should work.
http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html
e.g.
BEGIN TRANSACTION
new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1
-- some more work here
INSERT INTO table (id, ...) VALUES (new_id, ...)
COMMIT
Row level locking can only lock rows that exist. Creating new rows (that
would have an influence on my MAX value) are still possible and thus row
level locking is not what I need. I really need locking an entire table
for every other read or write access.
InnoDB can also lock the gap, which will prevent new rows that would
have been returned by the SELECT. The manual has more info on this in
the section on consistent reads in InnoDB. FOR UPDATE will do what you
need.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
