Joao, ----- Original Message ----- From: "Joćo Borsoi" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, December 01, 2002 1:20 AM Subject: Innob next-key locking, please help!
> Hello, > > I have a question about Innob next-key locking. I'm using mysql 3.23.49. > The following is writen on the manual: > > "You can use the next-key locking to implement a uniqueness check in > your application: if you read your data in share mode and do not see a > duplicate for a row you are going to insert, then you can safely insert > your row and know that the next-key lock set on the successor of your > row during the read will prevent anyone meanwhile inserting a duplicate > for your row. Thus the next-key locking allows you to 'lock' the > non-existence of something in your table." > > Than I tried to simulate with two mysql monitors windows (process 1 and > 2): > > process 1 > --------- > mysql> begin; > Query OK, 0 rows affected (0.00 sec) > > mysql> select * from LOGON_Chunk where chunkId=5 LOCK IN SHARE MODE; > Empty set (0.00 sec) > > process 2 > --------- > mysql> insert into LOGON_Chunk values (5, 'chunk5', 'meaning5', NULL, > NULL, NULL); > > > I got no answer for process 2. Then I got back to process 1: > > process 1 > --------- > mysql> insert into LOGON_Chunk values (5, 'chunk5', 'meaning5', NULL, > NULL, NULL); > ERROR 1213: Deadlock found when trying to get lock; Try restarting > transaction > > and on process 2 I got: > Query OK, 1 row affected (12.83 sec) > > > Is that correct? The manual says I can lock the non-existence of > something but I couldn't. Can anyone help me? What am I doing wrong? I > also tried selecting for update and got the same result. please test 4.0.5a. " MySQL/InnoDB-4.0.3, August 28, 2002 Removed unnecessary deadlocks when inserts have to wait for a locking read, update, or delete to release its next-key lock. ... " > Thanks, > Joao. > > sql, query. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.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