Re: Innodb Locks

Tue, 10 Oct 2006 11:34:56 -0700

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]























					Reply via email to