RE: Innodb Locks
I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Yup, innodb_locks_unsafe_for_binlog=1 fixes the problem and so does your suggestion of using a JOIN instead of a subselect. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 8:54 AM To: Baron Schwartz; Rick James Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- 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 Locks
It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals
RE: Innodb Locks
It probably uses a single lock to handle a JOIN, and two locks to handle a sub-SELECT. I doubt that it helps, but if I'm right it will change what you see when you poking around. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 2:42 PM To: Baron Schwartz Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after
RE: Innodb Locks
Right, as I understand it the query optimizer in 5.2 will simply rewrite these sub selects as joins when possible. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:45 AM To: Robert DiFalco; 'Baron Schwartz' Cc: 'Rick James'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks It probably uses a single lock to handle a JOIN, and two locks to handle a sub-SELECT. I doubt that it helps, but if I'm right it will change what you see when you poking around. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 2:42 PM To: Baron Schwartz Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED
RE: Innodb Locks
Oops, I should have emphasized that the TEMPORARY TABLE should be MyISAM or
Memory so as to avoid locking on it. (This _assumes_ that it is ok to split
the SELECT and DELETE into separate transactions. Often the semantics of
such a move allow such. YMMV)
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:50 AM
To: Jerry Schwartz; Baron Schwartz
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Innodb Locks
Right, as I understand it the query optimizer in 5.2 will
simply rewrite
these sub selects as joins when possible.
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:45 AM
To: Robert DiFalco; 'Baron Schwartz'
Cc: 'Rick James'; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Innodb Locks
It probably uses a single lock to handle a JOIN, and two
locks to handle
a sub-SELECT. I doubt that it helps, but if I'm right it will change
what you see when you poking around.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 2:42 PM
To: Baron Schwartz
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Innodb Locks
Then I guess I am not understanding why re-writing the
statement as a
JOIN alleviates that need.
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:35 AM
To: Robert DiFalco
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: Re: Innodb Locks
It's not a bug in InnoDB. There are far more knowledgeable people
than I on this list, but it should get a share-mode lock on
anything
it selects from, otherwise there might be inconsistencies
as it tries
to serialize different transactions into the binary log for
replication.
If the statement reads different values from Elems on the
master and
slave, there'll be different results. So locking is necessary for
replication to work right.
If I'm glossing over the finer points too much, it's
because I don't
know them.
Someone else can surely correct me :-)
Baron
Robert DiFalco wrote:
I think what is strange to me is that InnoDB is locking on the
subquery table at all. Here's another example:
DELETE
FROM Vers
WHERE (
Vers.elementID IN (
SELECT Elems.ID
FROM Elems
WHERE (Elems.nodeID = ?)))
Disregarding whether performance would be better or worse
with a JOIN,
what I find odd is that this DELETE statement on Vers seems to be
putting locks on Elems. Might this be a bug in InnoDB?
Innotop has
this to say:
Locks Held and Waited For
Txn What Mode DB Tbl Index Heap Special
Ins Intent
1 waits_for Xte elems PRIMARY2 rec but not gap
0
Not that Txn 1 is an UPDATE on a single row of the ELEMS
table and it
is waiting for the LOCK from the above DELETE FROM Vers to be
released. I'm not sure why the DELETE statement is locking the
subquery table ELEMS which is simply being queried. Do I
*really* need
to change all of these to write the subquery to a temporary
table in
order to gain better concurrency?
R.
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 1:39 PM
To: Rick James
Cc: Robert DiFalco; mysql@lists.mysql.com;
[EMAIL PROTECTED]
Subject: Re: Innodb Locks
There is a detailed write-up on how locking works in the manual:
http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
If you are not doing replication, you might check out
innodb_locks_unsafe_for_binlog as mentioned in
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter
Z also wrote an article on this:
http://www.mysqlperformanceblog.com/category/replication/
You may get better performance from using a JOIN instead
of an IN()
subquery. You will have to test. Sometimes it is much better,
sometimes worse. Usually better in my experience. Making the
long-running query as short as possible is probably a good idea.
Maybe you can break it up into several queries so it
doesn't try to
lock so many rows at once. There could be many other
approaches too,
it just depends on your needs and data.
Without altering how locks are handled with startup options, the
temporary table approach will avoid the locks only if you
COMMIT after
the CREATE... SELECT. The other subquery approach will not avoid
them.
I'm not sure if I should be replying to both the 'internals
RE: Innodb Locks
Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- 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 Locks
Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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 Locks
On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
No foreign key relationships. If I pull it into a temp table or a separate query that I then iterate through for all the updates on AnotherTable, then all works well. Odd. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:09 AM To: mysql@lists.mysql.com Subject: Re: Innodb Locks On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- 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 Locks
There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locks disappear
Heikki, Yep. That's why I use seperate connections for holding the lock and to do the subsequent locking attempts. Besides, if that were the problem, I would see the lock disappear at the very first failed locking attempt, but that's not the case. I thought it might be a connection timeout, but the settings don't seem to allow for that (unless my debug session takes 8 hours). Aargh, why isn't there a nice log that tells me why the lock is released. Or is there? I remember a debug-option, do you think that may work? Wouter Zelle - Original Message - From: Wouter Zelle [EMAIL PROTECTED] Unfortunately it is not that easy. I've set the innodb_lock_wait_timeout to 1 because I want locks to fail quickly, so my program can move on to the next request. In pseudocode: Fetch a bunch of requests with status=unprocessed Try to obtain a lock through a select * from x for update If lock: process If lock-timeout: move on to the next request. This works perfectly except that the locks disappear suddenly for no good reason at all. This takes far longer than the did you take into account that a lock wait timeout error rolls back the WHOLE transaction and releases ALL locks of that transaction? Regards, Heikki -- sql, query, stupid filter - 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
Re: InnoDB locks disappear
Wouter, the Lock Monitor output tells that trx 370099 has been committed or rolled back by the user. That is why the locks have disappeared. Have you taken into account the following: 8.5 When does MySQL implicitly commit or rollback a transaction? a.. MySQL has the autocommit mode switched on in a session if you do not do set autocommit=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. b.. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. c.. The following SQL statements cause an implicit commit of the current transaction in MySQL: ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES. The CREATE TABLE statement in InnoDB is processed as a single transaction which is independent of the current user transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. d.. If you in the autocommit mode use LOCK TABLES it is like BEGIN TRANSACTION in the sense that it switches the autocommit mode off until you call UNLOCK TABLES. e.. If you you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. Regards, Heikki .. Heikki, Yep. That's why I use seperate connections for holding the lock and to do the subsequent locking attempts. Besides, if that were the problem, I would see the lock disappear at the very first failed locking attempt, but that's not the case. I thought it might be a connection timeout, but the settings don't seem to allow for that (unless my debug session takes 8 hours). Aargh, why isn't there a nice log that tells me why the lock is released. Or is there? I remember a debug-option, do you think that may work? Wouter Zelle - Original Message - From: Wouter Zelle [EMAIL PROTECTED] Unfortunately it is not that easy. I've set the innodb_lock_wait_timeout to 1 because I want locks to fail quickly, so my program can move on to the next request. In pseudocode: Fetch a bunch of requests with status=unprocessed Try to obtain a lock through a select * from x for update If lock: process If lock-timeout: move on to the next request. This works perfectly except that the locks disappear suddenly for no good reason at all. This takes far longer than the did you take into account that a lock wait timeout error rolls back the WHOLE transaction and releases ALL locks of that transaction? Regards, Heikki -- sql, query, stupid filter - 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
Re: InnoDB locks disappear
Wouter, now that I read the query log more carefully I notice that it was thread 23 who was holding the row locks. From the query log wee see that thread 23 says 'Quit' around 14:47. Could it be that you have some 5 minute timeout for a silent connection? When the connection is terminated, MySQL rolls back the transaction, which explains why the locks disappeared. Regards, Heikki ... I've cleaned up the log a bit for easy reading. --- 14:42:42 23 Connect wouter@localhost on 23 Init DB rosetta 23 Query SHOW VARIABLES 23 Query SET autocommit=0 23 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:43:13 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 22 Query commit 14:43:18 24 Connect wouter@localhost on 24 Init DB rosetta 24 Query SHOW VARIABLES 24 Query SET autocommit=0 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:43:43 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 22 Query commit 14:44:12 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:44:33 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 22 Query commit 14:44:53 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:45:18 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 22 Query commit 14:45:24 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:45:42 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 22 Query commit 14:45:54 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:46:23 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 22 Query commit 14:46:41 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA USER T1 WHERE (t0.EXTERNAL IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER ID = T1.USER ID FOR UPDATE 14:47:08 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION DATE ASC 14:47:09 22 Query commit 23 Quit 14:47:283 Query DROP TABLE innodb lock monitor ---End Query Log - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, September 06, 2002 7:20 PM Subject: Re: InnoDB locks disappear Wouter, the Lock Monitor output tells that trx 370099 has been committed or rolled back by the user. That is why the locks have disappeared. Have you taken into account the following: 8.5 When does MySQL implicitly commit or rollback a transaction? a.. MySQL has the autocommit mode switched on in a session if you do not do set autocommit=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. b.. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. c.. The following SQL statements cause an implicit commit of the current transaction in MySQL: ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES. The CREATE TABLE statement in InnoDB is processed as a single transaction which is independent of the current user transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. d.. If you in the autocommit mode use LOCK TABLES it is like BEGIN TRANSACTION in the sense that it switches the autocommit mode off until you call UNLOCK TABLES. e.. If you you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. Regards, Heikki .. Heikki, Yep. That's why I use seperate connections for holding the lock and to do the subsequent locking attempts. Besides, if that were
Re: InnoDB locks disappear
Wouter, - Original Message - From: Wouter Zelle [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, September 04, 2002 10:59 AM Subject: Re: InnoDB locks disappear Heikki, Unfortunately it is not that easy. I've set the innodb_lock_wait_timeout to 1 because I want locks to fail quickly, so my program can move on to the next request. In pseudocode: Fetch a bunch of requests with status=unprocessed Try to obtain a lock through a select * from x for update If lock: process If lock-timeout: move on to the next request. This works perfectly except that the locks disappear suddenly for no good reason at all. This takes far longer than the did you take into account that a lock wait timeout error rolls back the WHOLE transaction and releases ALL locks of that transaction? Regards, Heikki innodb_lock_wait_timeout. On second thought, it doesn't seem to be a timer at all, because the locks don't end sooner if I step through my program in the debugger. I'm not sure, but it seems to take a number of queries to do it. Could InnoDB release locks after x failed attempts to lock the same row? The Inno Lock Monitor is supposed to be useful for debugging, does the report in my original post contain anything that could cause this? I think that the table locks seem fishy since I only use record locks at that point in my code. Could there be something wrong with that? Wouter Zelle --- Part of the INNODB MONITOR OUTPUT -- TABLE LOCK table rosetta/request trx id 0 370099 lock_mode IX RECORD LOCKS space id 0 page no 50 n bits 80 table rosetta/request index PRIMARY trx id 0 370099 lock_mode X Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; Record lock, heap no 2 RECORD: info bits 0 0: len 8; hex 80c5; asc . ...;; 1: Record lock, heap no 10 RECORD: info bits 0 0: len 8; hex 80e8; asc ...Þ;; 1: Suppressing further record lock prints for this page TABLE LOCK table rosetta/rosetta_user trx id 0 370099 lock_mode IX RECORD LOCKS space id 0 page no 53 n bits 288 table rosetta/rosetta_user index P RIMARY trx id 0 370099 lock_mode X Record lock, heap no 220 RECORD: info bits 0 0: len 8; hex 8d28; asc ...(;; 1: --- the default for innodb_lock_wait_timeout is 50 seconds in recent versions. It may be that the manual at www.mysql.com is outdated and claims it is infinite. A lock wait timeout rolls back the whole transaction and releases all locks. You should check the return value of your queries and look if they are reporting lock wait timeouts or other errors. I would really appreciate some help, Wouter Zelle Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -- - 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 - 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
Re: InnoDB locks disappear
Heikki, Unfortunately it is not that easy. I've set the innodb_lock_wait_timeout to 1 because I want locks to fail quickly, so my program can move on to the next request. In pseudocode: Fetch a bunch of requests with status=unprocessed Try to obtain a lock through a select * from x for update If lock: process If lock-timeout: move on to the next request. This works perfectly except that the locks disappear suddenly for no good reason at all. This takes far longer than the innodb_lock_wait_timeout. On second thought, it doesn't seem to be a timer at all, because the locks don't end sooner if I step through my program in the debugger. I'm not sure, but it seems to take a number of queries to do it. Could InnoDB release locks after x failed attempts to lock the same row? The Inno Lock Monitor is supposed to be useful for debugging, does the report in my original post contain anything that could cause this? I think that the table locks seem fishy since I only use record locks at that point in my code. Could there be something wrong with that? Wouter Zelle --- Part of the INNODB MONITOR OUTPUT -- TABLE LOCK table rosetta/request trx id 0 370099 lock_mode IX RECORD LOCKS space id 0 page no 50 n bits 80 table rosetta/request index PRIMARY trx id 0 370099 lock_mode X Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; Record lock, heap no 2 RECORD: info bits 0 0: len 8; hex 80c5; asc . ...;; 1: Record lock, heap no 10 RECORD: info bits 0 0: len 8; hex 80e8; asc ...Þ;; 1: Suppressing further record lock prints for this page TABLE LOCK table rosetta/rosetta_user trx id 0 370099 lock_mode IX RECORD LOCKS space id 0 page no 53 n bits 288 table rosetta/rosetta_user index P RIMARY trx id 0 370099 lock_mode X Record lock, heap no 220 RECORD: info bits 0 0: len 8; hex 8d28; asc ...(;; 1: --- the default for innodb_lock_wait_timeout is 50 seconds in recent versions. It may be that the manual at www.mysql.com is outdated and claims it is infinite. A lock wait timeout rolls back the whole transaction and releases all locks. You should check the return value of your queries and look if they are reporting lock wait timeouts or other errors. I would really appreciate some help, Wouter Zelle Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -- - 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
Re: InnoDB locks disappear
Wouter, - Original Message - From: Wouter Zelle [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, September 03, 2002 7:43 PM Subject: InnoDB locks disappear My program uses locks to allow for multi-threading (processing requests that are stored in the database using more than one thread and/or application). The problem is that the locks disappear for seemingly no reason at all. The same queries are repeated over and over again until the lock just vanishes, so it seems to be a time-out or something like that. I've created a log-file of the queries and two reports from the innodb_lock_monitor, the one just before the lock vanishes and the one after. the default for innodb_lock_wait_timeout is 50 seconds in recent versions. It may be that the manual at www.mysql.com is outdated and claims it is infinite. A lock wait timeout rolls back the whole transaction and releases all locks. You should check the return value of your queries and look if they are reporting lock wait timeouts or other errors. I would really appreciate some help, Wouter Zelle Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB ---Query log -- Comments: At the start of the fragment the request is being locked in connection #23 (every request is locked in a seperate connection so commits won't do harm anyone else). The next query (14:43:13) finds all unprocessed requests in the DB (Connection #22 is used for this query). The 'For Update'-query tries to lock the request and fails if it is already locked (causing my app to move on to the next request). Connection #24 is used for this kind of query after 14:43:18. As you can see, the two types of requests keep interleaving until I halted the app, which was after the request was unlocked. I've cleaned up the log a bit for easy reading. --- 14:42:42 23 Connect wouter@localhost on 23 Init DB rosetta 23 Query SHOW VARIABLES 23 Query SET autocommit=0 23 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:43:13 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 22 Query commit 14:43:18 24 Connect wouter@localhost on 24 Init DB rosetta 24 Query SHOW VARIABLES 24 Query SET autocommit=0 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:43:43 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 22 Query commit 14:44:12 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:44:33 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 22 Query commit 14:44:53 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:45:18 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 22 Query commit 14:45:24 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:45:42 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 22 Query commit 14:45:54 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:46:23 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 22 Query commit 14:46:41 24 Query SELECT [t0.columns] FROM REQUEST t0, ROSETTA_USER T1 WHERE (t0.EXTERNAL_IDENTIFIER = 'RequestID01' AND T1.LOGIN = 'login') AND t0.USER_ID = T1.USER_ID FOR UPDATE 14:47:08 22 Query SELECT [t0.columns] FROM REQUEST t0 WHERE t0.STATUS = 'ReadyForProcessing' ORDER BY t0.CREATION_DATE ASC 14:47:09 22 Query commit 23 Quit 14:47:283 Query DROP TABLE innodb_lock_monitor ---End Query Log ---INNODB MONITOR OUTPUT -- Comments: I took out everything but the transactions, since I don't think the rest will be of any help. If you need some other info, please ask. -- ---
Re: innodb locks
Hi! Copied message: .. Hi, I'm a regular mysql user, and a very newbie to innodb. :) I would like to use innodb because of its row-level locking feature. Presently I have to use table locks, and they're causing lots of speed problems. (I've tried Gemini tables, but mysql always crashed even with the simplest tests. Innodb seems to be very stable so far.) I could not find anything in the docs about whether or not the following can be done: I have 2 tables, one with data about users, and one with market data. (This is a game, and users can buy and sell their stuff.) The market database has the following relevant columns: good, quantity, price, owner. When someone buys a particular type of good, I would like to lock those rows from the table, but since the quantity will change, I don't even want other users to see these rows until I'm finished. When I lock the entire table, I can do this. When I use for update, or lock in share mode, other threads cannot update, but they can still see these rows. Is there a way to lock ROWS, so they're not VISIBLE until the first thread is done with them? I would like to be able to use other rows though from other threads. At this point, it seems to me that I can only do this by locking the entire table.Any suggestions? Thanks, Attila ... Perhaps you want to query the table with SELECT ... LOCK IN SHARE MODE ? Regards, Heikki http://www.innodb.com/ibman.html - 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
Re: innodb locks
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 20 Nov 2001, Heikki Tuuri wrote: When someone buys a particular type of good, I would like to lock those rows from the table, but since the quantity will change, I don't even want other users to see these rows until I'm finished. When I lock the entire table, I can do this. When I use for update, or lock in share mode, other threads cannot update, but they can still see these rows. Is there a way to lock ROWS, so they're not VISIBLE until the first thread is done with them? I would like to be able to use other rows though from other threads. ... Perhaps you want to query the table with SELECT ... LOCK IN SHARE MODE ? Nope, doesn't do what I want. I'll try to draw a table, with some example data: THREAD A THREAD B 1. mysql begin work; Query OK, 0 rows affected (0.00 sec) 2. mysql begin work; Query OK, 0 rows affected (0.00 sec) 3. mysql select * from teszt where id 10 lock in share mode; ++--+ | id | tmp | ++--+ | 1 | 1408 | | 2 | 1409 | | 2 | 1407 | | 3 | 1404 | | 4 | 1403 | | 5 | 1402 | | 5 | 1409 | | 6 | 1401 | | 7 | 1400 | | 8 | 1399 | | 9 | 1397 | ++--+ 11 rows in set (0.03 sec) 4. mysql select * from teszt where id 5 lock in share mode; ++--+ | id | tmp | ++--+ | 1 | 1408 | | 2 | 1409 | | 2 | 1407 | | 3 | 1404 | | 4 | 1403 | ++--+ 5 rows in set (0.03 sec) This happens now. What I would like is #4 to wait, until thread A finishes (commits OR rolls back). id is a key, buy not a unique key, since there can be several rows of the same value there. If it were a unique key, my wish would come true. :) If, instead of lock in share mode, I try for update, then it works, but not how I'd like it. Then #4 will wait, but it will wait even if the query is select * from teszt where id 15 for update. So it's basically like locking the entire table, and I cannot work with other data. It WORKS this way, I was just trying to speed it up. If this particular case cannot be solved otherwise, I'm still happy, because I could get rid of 90% of the lock tables. That's still better than nothing. :) Except... see next mail, on a different topic. :) Thanks, Attila ... In nature there are neither rewards nor punishments; there are consequences. -Robert Green Ingersoll, lawyer and orator (1833-1899) - --- Public key: http://civ.hu/attila.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7+iboDeyfLhmXxQwRAuMEAJ9WDB2EgkxDqlZ8ZQEl+aBGtLlyZACeISaS oKldGkk/nJIRGkmyXdtDwC8= =swnC -END PGP SIGNATURE- - 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
