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.

Robert DiFalco wrote:
> 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.
>

Yes it will. You are updating SomeTable.id in one transaction and
referencing it in another transaction.

Allowing the change your first transaction while referencing it in the
second transaction could lead to an inconsistency in your database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

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=r...o@tripwire.com

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@tripwire.com]
> Sent: Tuesday, October 03, 2006 9:26 AM
> To: mysql@lists.mysql.com; internals@lists.mysql.com
> 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=r...o@tripwire.com
>
>
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:
> http://lists.mysql.com/internals?uns...@yahoo-inc.com
>
>
>

Does anybody know how can I see what queries are currently being
executed?


Thanks

On Tuesday 03 October 2006 10:57, Feliks Shvartsburd wrote:
> Does anybody know how can I see what queries are currently being
> executed?

From the mysql commandline, use "show processlist;"

There is also a program called mytop which shows what's
executing.

-Chris

show processlist gives you an abbreviated list of queries.

show full processlist gives you the full queries.



>-----Original Message-----
>From: Feliks Shvartsburd [mailto:fshvartsburd@syndero.com]
>Sent: Tuesday, October 03, 2006 1:57 PM
>To: mysql@lists.mysql.com; internals@lists.mysql.com
>Subject: Question
>
>Does anybody know how can I see what queries are currently being
>executed?
>
>
>Thanks
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql?unsub=glaw@ionosphere.net
>
>

On 10/2/06, Robert DiFalco wrote:
> Is there a detailed source for when innodb creates row or table locks?

The sourcecode.

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

It shouldn't from what you have described here. But might there be
foreign key relation sbetween both tables?

Jochem

It's possible that mysql couldn't create the SOCKet file...
For emergency connection use:

mysql -u root -h 127.0.0.1 -p

Do not use "localhost" as this instructs the client to go through the
socket... but if you say 127.0.0.1 the client will use TCP...

Next... make sure that mysql can indeed create the socket under
/tmp... check the permisions... and I must say this location is
strange I have /var/lib/mysql/mysql.sock ... also check this out...

To set it to another location use my.cnf file


Good luck !


On 10/3/06, Feliks Shvartsburd <fshvartsburd@syndero.com> wrote:
> Hi
>
> I have several problems. I'm using MySql 5 and it is running on Linux.
> When I'm trying to execute mysql -u root -p I get the following:
>
> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> '/tmp/mysql.sock' (111)
>
> I'm also not able to stop the server. When I run mysql.server stop it
> gives me some garbage. Please help.
>
> Thanks
>
>
>
> -----Original Message-----
> From: George Law [mailto:glaw@IONOSPHERE.net]
> Sent: Tuesday, October 03, 2006 11:25 AM
> To: Feliks Shvartsburd; mysql@lists.mysql.com; internals@lists.mysql.com
> Subject: RE: Question
>
> show processlist gives you an abbreviated list of queries.
>
> show full processlist gives you the full queries.
>
>
>
> >-----Original Message-----
> >From: Feliks Shvartsburd [mailto:fshvartsburd@syndero.com]
> >Sent: Tuesday, October 03, 2006 1:57 PM
> >To: mysql@lists.mysql.com; internals@lists.mysql.com
> >Subject: Question
> >
> >Does anybody know how can I see what queries are currently being
> >executed?
> >
> >
> >Thanks
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
> >http://lists.mysql.com/mysql?unsub=glaw@ionosphere.net
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=g...reda@gmail.com
>
>


--
-- -- -- -- -- -- -- -- -- -- -- -- -- --
Gabriel PREDA
Senior Web Developer

There is a detailed write-up on how locking works in the manual:

http://dev.mysql.com/doc/refman/5.0/...ion-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/...arameters.html. Peter Z
also wrote an article on this: http://www.mysqlperformanceblog.com/...y/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@tripwire.com]
>> Sent: Tuesday, October 03, 2006 9:26 AM
>> To: mysql@lists.mysql.com; internals@lists.mysql.com
>> 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=r...o@tripwire.com
>>
>>
>>
>>
>> --
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:
>> http://lists.mysql.com/internals?uns...@yahoo-inc.com
>>
>>
>>
>
>

--
Baron Schwartz
http://www.xaprb.com/