When create a deadlock in mysql(innodb) as the the example on Page 10 of "High performance MySQL". But if I update one line of the test in one transaction, update another line will be blocked and finally timeout. It is like innodb use the table level lock rather than row level lock when update with where condition. This situation is not accord with the row level locks of innodb.
The Mysql Version:
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using EditLine wrapper
Connection id: 2
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.26 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 52 min 1 sec
Threads: 3 Questions: 107 Slow queries: 0 Opens: 69 Flush tables: 1 Open tables: 62 Queries per second avg: 0.006
--------------
mysql> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
Create test table:
mysql> show create table t\G;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a1` int(11) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from t;
+------+------+------+
| a1 | b | c |
+------+------+------+
| 1 | a | b |
| 2 | aa | bb |
+------+------+------+
2 rows in set (0.00 sec)
Then open two independent sessions to create two transactions
Session 1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set b='x' where a1=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
In sessions 2, update will be blocked and finally timeout
mysql> start transaction
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c='yy' where a1=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
During block, use InnoDB plugin to get the locking relationship
mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread,
-> r.trx_query waiting_query,
-> b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
-> b.trx_query blocking_query
-> FROM information_schema.innodb_lock_waits w
-> INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
-> INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+--------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+--------------------------------+-----------------+-----------------+----------------+
| 5933 | 6 | update t set c='yy' where a1=1 | 5932 | 5 | NULL |
+----------------+----------------+--------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
In principle, the row lock in session 1 does not block updates in session 2.
If you have encountered such a problem, will you please help explain why session 2 was blocked.
Do not consider using transactions in InnoDB without a PRIMARY KEY.
And seriously consider using at least INDEX(a1) when doing UPDATE ... WHERE ai = constant.
Otherwise, InnoDB gets rather sloppy with "row level locking" -- probably because it is having trouble getting a grip on each "row" without indexing.