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 X te elems PRIMARY 2 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
