Consider the following queries, each will lock the database in different ways. In this post Im going to show you how you see where each one is locking, and how to optimise it.
update TabFoo set bla = ‘bla’ where id = 1;
update TabFoo set bla = ‘bla’ where foo = ‘bla';
To see the locks in MySQL innodb, start a transaction run the query, then inspect the innodb status. Finally rollback or commit the transaction. We need to do this inside a transaction so the locks are held long enough for us to see them. If we don’t use a transaction, these locks will still be used, but they will happen very quickly.
begin transaction;
update TabFoo set bla = ‘bla’ where id = 1;
show engine innodb status;
rollback;
In the innodb status, look for the section Transactions. Here we can see it has 1 row lock. Thats great and what you’d expect for a simple update by a primary key.
------------
TRANSACTIONS
------------
Trx id counter 4830020
Purge done for trx's n:o < 4830017 undo n:o < 0 state: running but idle
History list length 472
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 27, OS thread handle 0x1300ef000, query id 4320 localhost 127.0.0.1 root cleaning up
---TRANSACTION 4830019, ACTIVE 3 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 28, OS thread handle 0x130133000, query id 4322 localhost 127.0.0.1 root init
Full table locking
In my test database table I only have 13 rows, and its locking them all. This query locks the whole table because it doesn’t know how many rows match the query, it could be all of them. This can been seen with “explain plan” of the query.
update TabFoo set bla = ‘bla’ where foo = ‘bla';
---TRANSACTION 4830029, ACTIVE 4 sec
2 lock struct(s), heap size 360, 13 row lock(s), undo log entries 2
MySQL thread id 28, OS thread handle 0x130133000, query id 4369 localhost 127.0.0.1 root init
Optimising locking by adding an index.
If we give the table a index on the column the database will be able to optimise its locking to just the rows that it knows match the query.
create index IX_TabFoo_bla on TabFoo(blah);
update TabFoo set bla = ‘bla’ where foo = ‘bla';
---TRANSACTION 4830039, ACTIVE 3 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 28, OS thread handle 0x130133000, query id 4411 localhost 127.0.0.1 root init As you can see its locking 5 rows, not all of them. I actually only have 2 rows that match the query so its strange that its locking more than that, there may be some internal database reason, maybe its locked the table page or something, but its a lot better.