List:General Discussion« Previous MessageNext Message »
From:Rick James Date:October 10 2006 7:31pm
Subject:RE: Innodb Locks
View as plain text  
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:rdifalco@stripped] 
> Sent: Tuesday, October 10, 2006 11:50 AM
> To: Jerry Schwartz; Baron Schwartz
> Cc: Rick James; mysql@stripped; internals@stripped
> 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:jschwartz@stripped] 
> Sent: Tuesday, October 10, 2006 11:45 AM
> To: Robert DiFalco; 'Baron Schwartz'
> Cc: 'Rick James'; mysql@stripped; internals@stripped
> 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:rdifalco@stripped]
> > Sent: Tuesday, October 10, 2006 2:42 PM
> > To: Baron Schwartz
> > Cc: Rick James; mysql@stripped; internals@stripped
> > 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:baron@stripped]
> > Sent: Tuesday, October 10, 2006 11:35 AM
> > To: Robert DiFalco
> > Cc: Rick James; mysql@stripped; internals@stripped
> > 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:baron@stripped]
> > > Sent: Tuesday, October 03, 2006 1:39 PM
> > > To: Rick James
> > > Cc: Robert DiFalco; mysql@stripped; 
> internals@stripped
> > > 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:rdifalco@stripped]
> > >>> Sent: Tuesday, October 03, 2006 9:26 AM
> > >>> To: mysql@stripped; internals@stripped
> > >>> 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/mysql?unsub=1
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> MySQL Internals Mailing List
> > >>> For list archives: http://lists.mysql.com/internals To 
> > >>> unsubscribe:
> > >>> http://lists.mysql.com/internals?unsub=1
> > >>>
> > >>>
> > >>>
> > >>
> > >
> >
> > --
> > Baron Schwartz
> > http://www.xaprb.com/
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> >
> >
> 
> 
> 
> 
> 
> 
> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    
> http://lists.mysql.com/internals?unsub=1
> 
> 
> 

Thread
Innodb LocksRobert DiFalco2 Oct
  • RE: Innodb LocksRobert DiFalco3 Oct
    • RE: Innodb LocksRick James3 Oct
      • QuestionFeliks Shvartsburd3 Oct
        • Re: QuestionChris Comparini3 Oct
          • RE: QuestionFeliks Shvartsburd3 Oct
        • RE: QuestionGeorge Law3 Oct
          • RE: QuestionFeliks Shvartsburd3 Oct
            • Re: QuestionGabriel PREDA3 Oct
      • Re: Innodb LocksBaron Schwartz3 Oct
  • Re: Innodb LocksJochem van Dieten3 Oct
    • RE: Innodb LocksRobert DiFalco3 Oct