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]
