MYISAM表锁：

Table_locks_immediate值越大表示表级锁定越严重。

mysql> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

session 1:

mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)

没加读锁的表不能访问：

mysql> select * from t;
ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES

mysql> select * from t1;
+------+
| name |
+------+
| 111 |
+------+
1 row in set (0.00 sec)

通过别名的访问也会报错：

mysql> select * from t1 as t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

需要锁定别名：

mysql> lock table t1 as a read,t1 as t2 read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 as t2;
+------+
| name |
+------+
| 111 |
+------+
1 row in set (0.00 sec)

session 2:

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 32768 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

INNODB锁争用的问题：

mysql> select * FROM information_schema.INNODB_LOCKS;
Empty set (0.00 sec)

mysql> select * FROM information_schema.INNODB_LOCK_WAITS;
Empty set (0.00 sec)

mysql> create table innodb_monitor(a int) engine=innodb;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2015-07-21 13:19:48 7f4102434700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 10326 srv_idle
srv_master_thread log flush and writes: 10328
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21
OS WAIT ARRAY INFO: signal count 21
Mutex spin waits 10, rounds 300, OS waits 10
RW-shared spins 11, rounds 330, OS waits 11
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 14893
Purge done for trx's n:o < 14886 undo n:o < 0 state: running but idle
History list length 283
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f41023f3700, query id 323 196.128.1.123 root cleaning up
---TRANSACTION 14889, not started
MySQL thread id 2, OS thread handle 0x7f4102434700, query id 337 localhost root init
show engine innodb status
---TRANSACTION 14873, not started
mysql tables in use 1, locked 1
MySQL thread id 1, OS thread handle 0x7f4102475700, query id 329 localhost root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
521 OS file reads, 64 OS file writes, 30 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.14 writes/s, 0.59 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 0 buffer(s)
0.00 hash searches/s, 0.77 non-hash searches/s
---
LOG
---
Log sequence number 370556050
Log flushed up to 370556050
Pages flushed up to 370556050
Last checkpoint at 370556050
0 pending log writes, 0 pending chkp writes
21 log i/o's done, 0.23 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------

session 1:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=178;
+-----+------+
| id | name |
+-----+------+
| 178 | aaa |
+-----+------+
1 row in set (0.00 sec)

mysql> select * from t where id=178 lock in share mode;
+-----+------+
| id | name |
+-----+------+
| 178 | aaa |
+-----+------+
1 row in set (0.00 sec)

mysql> update t set name='rrrrrr' where id=178;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

处于等待状态

session 2:
mysql> update t set name='rrrrrr' where id=178;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死循环退出

session 1执行成功。

在没有索引的情况下，INNODB会对所有的记录都加锁：

session 1:
mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tab_no_index values (1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_no_index where id=1;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id=1 for update;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

session 2:

mysql> select * from tab_no_index where id=2;
+------+------+
| id | name |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

有索引的情况：

session 1:

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tab_with_index values (1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table tab_with_index add index id(id);
Query OK, 0 rows affected (12.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from tab_with_index where id=1;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_with_index where id=1 for update;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

session 2:

mysql> select * from tab_with_index where id=2;
+------+------+
| id | name |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_with_index where id=2 for update;
+------+------+
| id | name |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)

如果是相同的索引键，是会出现锁冲突的：

mysql> truncate table tab_with_index;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tab_with_index values(1,'1'),(1,'4');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tab_with_index add index id(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

session 1:

mysql> select * from tab_with_index where name='1' for update;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

session 2:

mysql> truncate table tab_with_index;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tab_with_index values(1,'1'),(1,'4');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tab_with_index add index id(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from tab_with_index where id=1 and name='4' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

next-key锁：

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 32768 |
+----------+
1 row in set (0.03 sec)

mysql> create table t_test as select * from t;
Query OK, 32768 rows affected (0.68 sec)
Records: 32768 Duplicates: 0 Warnings: 0

mysql> delete from t where id > 101;
Query OK, 32704 rows affected (0.14 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

session 1:

mysql> select * from t where id = 102 for update;
Empty set (0.00 sec)

session 2阻塞:

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(201,'aaa');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 64 |
+----------+
1 row in set (0.00 sec)

mysql> delete from t where id >10;
Query OK, 56 rows affected (0.01 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | aaa |
| 4 | bbb |
| 6 | aaa |
| 7 | bbb |
| 8 | aaa |
| 9 | bbb |
+----+------+
8 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t66 as select * from t where 1=0;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into t66 select * from t where name='aaa';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t66;
Empty set (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | aaa |
| 4 | bbb |
| 6 | aaa |
| 7 | bbb |
| 8 | aaa |
| 9 | bbb |
+----+------+
8 rows in set (0.00 sec)

mysql> update t set name='ccc' where name='aaa';
Query OK, 4 rows affected (20.98 sec)
Rows matched: 4 Changed: 4 Warnings: 0

ITPub博客

mysql锁问题的研究

最新文章