MySQL Locking (Version 5.5)

Locking in MySQL seems more complicated to follow than in other database.  Monitoring in MySQL is better with the MySQL Enterprise Monitor but for all the command line people, it is more difficult to find out what is locking/blocking.


Main views are from information_schema - INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS.

Here is a quick guide. How to Find Blocking InnoDB Connections in MySQL.

As of MySQL 5.5, you can use these two information_schema views, INNODB_LOCK_WAITS and INNOB_TRX.

Here is an example :
session 1 :
create table t(id int not null auto_increment, c1 int, primary key(id));
insert into t(id,c1) values (1,1);

set autocommit = 0;
update t set c1 = 2 where id = 1;

session 2 :
update t set c1 = 3 where id = 1;


You can see that :
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 60A9
trx_state: LOCK WAIT
trx_started: 2012-02-23 12:50:22
trx_requested_lock_id: 60A9:4658:3:2
trx_wait_started: 2012-02-23 12:50:22
trx_weight: 2
trx_mysql_thread_id: 849
trx_query: update t set c1 = 3 where id = 1
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: 60A8
trx_state: RUNNING
trx_started: 2012-02-23 12:49:32
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 848
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.52 sec)

mysql> select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
requesting_trx_id: 60A9
requested_lock_id: 60A9:4658:3:2
blocking_trx_id: 60A8
blocking_lock_id: 60A8:4658:3:2
1 row in set (0.00 sec)

Transaction 60A8 is blocking 60A9. So if you want to kill 60A8, you need to kill the trx_mysql_thread_id of that transaction :
kill 848;

References -
http://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-waits-table.html
http://dev.mysql.com/doc/refman/5.5/en/information-schema.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-examples.html

This is a work-in-progress at the moment, as I get more information I will update the blog.