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 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 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]
