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. Thanks, Joao. sql, query. --------------------------------------------------------------------- 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
