RE: InnoDB locking 'non-existence' of a row
Hello Heikki, Thank you for your reply and your explanation. It clarifies things and explains some of the unexpected behavior I've observed (such as my own inability to insert to the gap after locking it). In general, is there a place where I can find a good discussion on the various locks used by InnoDB? I've seen references to various locks in InnoDB status output, but wasn't sure what each kind was (S-locks, X-locks, etc.). This would be a great aid for helping troubleshoot these kinds of problems. Thank you for your time, Alex Zeltser List: MySQL General Discussion Previous MessageNext Message From: Heikki Tuuri Date: January 21 2004 4:32am Subject: Re: InnoDB locking 'non-existence' of a row Alex, diagram: record1 'gap' record2 (User A holds a next-key lock on record2) InnoDB can lock the non-existence of a row in the 'gap'. But it cannot presently make another user B to wait before B acquires a lock on the gap. The reason is that B's cursor has already passed the gap when B ends up waiting for a next-key lock on record2. If we would allow user A to insert to the gap, then the cursor of B should be moved backwards, so that B's cursor would see the inserted record when A commits. Currently, InnoDB does not move a cursor backwards when a lock wait ends. Locks on gaps are purely inhibitive. That is, you can prevent other users from inserting to the gap, but you cannot guarantee that you yourself will be able to insert. In the general case, we cannot prevent 2 users acquiring conflicting locks on the same gap: gap1 delete_marked_record gap2 If A holds an X-lock on gap1 and B holds an X-lock on gap2, and purge removes the delete_marked_record, then the gaps merge, and both A and B hold an X-lock on the gap. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 20, 2004 9:20 PM Subject: RE: InnoDB locking 'non-existence' of a row Hi Joe, Thanks for your reply. Actually, in my experience (and according to the = docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row = for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert = the row from another transaction will block or fail (until the first one does a commit or a = rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction = 'lock' the non-existence of a row with a select, and another wait until the lock is released--also = with a select. I've considered doing what you propose with a separate lock table, and may still do just = that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi,=20 Selecting a non-existent row won't acquire any locks that prevents = inserts from happening. One way to accomplish what you want is to create a separate insert lock table = consisting of a table name and a lock counter. Add a row for each table that you want to have these = insert locks on, and before performing any inserts, either update the corresponding row in the = insert lock table or select it for an update.=20 joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, =20 Thanks for the response and the suggestions. Doesn't SERIALIZABLE=20 level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than=20 that works just like the default REPEATABLE READ level? I've tried by = example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but=20 the results were the same. =20 Is there any way to make the second session block when both it and the = first one are 'locking' non-existence of a row? =20 Thanks in advance, =20 Alex =20 -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row =20 =20 Hi Alex! =20 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, =20 I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to = do this by using 'select ... for update', using the 'mysql' client=20 from two separate sessions as shown below: =20 Session 1: set AUTOCOMMIT=3D0; begin
Re: InnoDB locking 'non-existence' of a row
Alex, - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 10:28 PM Subject: RE: InnoDB locking 'non-existence' of a row Hello Heikki, Thank you for your reply and your explanation. It clarifies things and = explains some of the unexpected behavior I've observed (such as my own inability to insert to = the gap after locking it). In general, is there a place where I can find a good discussion on the = various locks used by InnoDB? I've seen references to various locks in InnoDB status output, but = wasn't sure what each kind was (S-locks, X-locks, etc.). This would be a great aid for helping = troubleshoot these kinds of problems. /mysql/innobase/lock/lock0lock.c contains a lengthy comment at the start of the file. And the full source code of the algorithms, too :). Papers by IBM researcher C. Mohan may also be of interest. Thank you for your time, Alex Zeltser Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
Hi Chris, Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ level? I've tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the results were the same. Is there any way to make the second session block when both it and the first one are 'locking' non-existence of a row? Thanks in advance, Alex -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
hi, Selecting a non-existent row won't acquire any locks that prevents inserts from happening. One way to accomplish what you want is to create a separate insert lock table consisting of a table name and a lock counter. Add a row for each table that you want to have these insert locks on, and before performing any inserts, either update the corresponding row in the insert lock table or select it for an update. joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ level? I've tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the results were the same. Is there any way to make the second session block when both it and the first one are 'locking' non-existence of a row? Thanks in advance, Alex -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB locking 'non-existence' of a row
Hi Joe, Thanks for your reply. Actually, in my experience (and according to the docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert the row from another transaction will block or fail (until the first one does a commit or a rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction 'lock' the non-existence of a row with a select, and another wait until the lock is released--also with a select. I've considered doing what you propose with a separate lock table, and may still do just that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi, Selecting a non-existent row won't acquire any locks that prevents inserts from happening. One way to accomplish what you want is to create a separate insert lock table consisting of a table name and a lock counter. Add a row for each table that you want to have these insert locks on, and before performing any inserts, either update the corresponding row in the insert lock table or select it for an update. joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than that works just like the default REPEATABLE READ level? I've tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the results were the same. Is there any way to make the second session block when both it and the first one are 'locking' non-existence of a row? Thanks in advance, Alex -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locking 'non-existence' of a row
Alex, diagram: record1 'gap' record2 (User A holds a next-key lock on record2) InnoDB can lock the non-existence of a row in the 'gap'. But it cannot presently make another user B to wait before B acquires a lock on the gap. The reason is that B's cursor has already passed the gap when B ends up waiting for a next-key lock on record2. If we would allow user A to insert to the gap, then the cursor of B should be moved backwards, so that B's cursor would see the inserted record when A commits. Currently, InnoDB does not move a cursor backwards when a lock wait ends. Locks on gaps are purely inhibitive. That is, you can prevent other users from inserting to the gap, but you cannot guarantee that you yourself will be able to insert. In the general case, we cannot prevent 2 users acquiring conflicting locks on the same gap: gap1 delete_marked_record gap2 If A holds an X-lock on gap1 and B holds an X-lock on gap2, and purge removes the delete_marked_record, then the gaps merge, and both A and B hold an X-lock on the gap. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Zeltser, Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 20, 2004 9:20 PM Subject: RE: InnoDB locking 'non-existence' of a row Hi Joe, Thanks for your reply. Actually, in my experience (and according to the = docs), if you select 'for update' or 'lock in share mode', you _can_ lock non-existence of a row = for inserts. In that case I think the 'gap' where the row would be is locked, and attempts to insert = the row from another transaction will block or fail (until the first one does a commit or a = rollback). Perhaps I'm misunderstanding what's happening? Unfortunately, what I'm trying to do is try to have one transaction = 'lock' the non-existence of a row with a select, and another wait until the lock is released--also = with a select. I've considered doing what you propose with a separate lock table, and may still do just = that, but first wanted to see if I can accomplish the same thing with some clever DB manipulation. Thanks! Alex -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:00 AM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: RE: InnoDB locking 'non-existence' of a row hi,=20 Selecting a non-existent row won't acquire any locks that prevents = inserts from happening. One way to accomplish what you want is to create a separate insert lock table = consisting of a table name and a lock counter. Add a row for each table that you want to have these = insert locks on, and before performing any inserts, either update the corresponding row in the = insert lock table or select it for an update.=20 joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: Hi Chris, =20 Thanks for the response and the suggestions. Doesn't SERIALIZABLE=20 level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than=20 that works just like the default REPEATABLE READ level? I've tried by = example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but=20 the results were the same. =20 Is there any way to make the second session block when both it and the = first one are 'locking' non-existence of a row? =20 Thanks in advance, =20 Alex =20 -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Friday, January 16, 2004 4:55 PM To: Zeltser, Alex Cc: [EMAIL PROTECTED] Subject: Re: InnoDB locking 'non-existence' of a row =20 =20 Hi Alex! =20 On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, =20 I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to = do this by using 'select ... for update', using the 'mysql' client=20 from two separate sessions as shown below: =20 Session 1: set AUTOCOMMIT=3D0; begin; select * from T where A =3D 'NOT_THERE' for update; =20 Session 2: set AUTOCOMMIT=3D0; begin; select * from T where A =3D 'NOT_THERE' for update; =20 What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. =20 Unfortunately, it didn't work that way. The 'select's in both=20 sessions returned right away, and it was only the subsequent=20 'insert's, 'update's and 'delete's that blocked. I can understand = the=20 rationale behind this behavior, but unfortunately it doesn't help me = with my problem. I'd like to be able to reliably check for = existence=20 of a record from two concurrent sessions and have the 'select' in = the=20 'second' session block
InnoDB locking 'non-existence' of a row
Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locking 'non-existence' of a row
Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
