对MySQL锁的一些理解测试

锁分共享锁（读锁）&排他锁（写锁）

锁粒度分表锁&行锁

表锁为最基本锁策略，也是开销最小的策略。

行锁可以最大程度支持并发处理，同时也带来最大的锁开销。

以下是我对锁做的一些测试：

隔离级别：repeatable-read

表设计：无索引主键情况

 CREATE TABLE `table1` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL,

  `age` tinyint(4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

root@test 03:01:54>select * from table1;

+------+------+------+

| id   | name | age  |

+------+------+------+

|    1 | aaa  |    1 |

|    2 | bbb  |    2 |

|    3 | ccc  |    3 |

+------+------+------+

3 rows in set (0.00 sec)

insert加锁情况：

事务1：

root@test 03:02:00>set autocommit =0;

Query OK, 0 rows affected (0.00 sec)

root@test 03:02:54>insert into table1 values(4,'ddd',4);

Query OK, 1 row affected (0.00 sec)

root@test 03:03:39>select * from table1;

+------+------+------+

| id   | name | age  |

+------+------+------+

|    1 | aaa  |    1 |

|    2 | bbb  |    2 |

|    3 | ccc  |    3 |

|    4 | ddd  |    4 |

+------+------+------+

4 rows in set (0.00 sec)

事务2：

root@test 03:00:15>set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

root@test 03:03:46>insert into table1 values(5,'ddd',5);

Query OK, 1 row affected (0.00 sec)

root@test 03:04:15>select * from table1;

+------+------+------+

| id   | name | age  |

+------+------+------+

|    5 | ddd  |    5 |

+------+------+------+

1 row in set (0.00 sec)

两者都未提交之前没有表锁，而且只能读自己未提交的事务。

update加锁情况：

事务1：

root@test 03:06:58>update table1 set name='fff' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务2：

root@test 03:06:54>update table1 set name='ggg' where id=2;   

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

root@test 03:07:34>

事务未提交前，为表锁。

delete加锁情况：

事务1：

root@test 03:08:43>delete from table1 where id=1;

Query OK, 1 row affected (0.00 sec)

事务2：

root@test 03:08:47>delete from table1 where id=2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

root@test 03:09:16>

同样，在未提交事务之前，为表锁。

测试二：对字段加主键索引后观察

root@test 03:10:54>show create table xbb\G

*************************** 1. row ***************************

       Table: xbb

Create Table: CREATE TABLE `xbb` (

  `id` int(11) NOT NULL DEFAULT '0',

  `name` varchar(255) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

root@test 03:13:02>select * from xbb;

+----+------+------+

| id | name | age  |

+----+------+------+

|  1 | stxm | NULL |

|  2 | xbb  | NULL |

|  3 | stxm | NULL |

|  4 | xbb  | NULL |

|  5 | icey | NULL |

|  6 | s    | NULL |

|  7 | t    | NULL |

+----+------+------+

7 rows in set (0.00 sec)

insert加锁情况同上，只加行锁。

update加锁情况：

事务1：

root@test 03:15:19>update xbb set name='dfdf' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务2：

root@test 03:15:21>update xbb set name='rere' where id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

由此可见：只加行锁。

delete加锁情况同上；

死锁测试：

死锁是指两个或两个以上的事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

事务1：

root@test 03:19:11>update xbb set name='sdsd' where id=3;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

root@test 03:23:16>update xbb set name='rrrr' where id=4;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

root@test 03:23:27>

事务2：

root@test 03:17:49>update xbb set age=34 where id=4;       

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

root@test 03:22:46>update xbb set age=9 where id=3;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

由此可见，事务1锁了id为3的行，事务2锁了id为4的行，结果对方都在相互等待对方释放，因此产生了死锁情况。

测试三：

间隙锁测试

事务1：

root@test 04:23:37>select * from xbb;

+----+------+------+

| id | name | age  |

+----+------+------+

|  1 | dfdf | NULL |

|  2 | rere | NULL |

|  3 | stxm |    4 |

|  4 | xbb  |   34 |

|  5 | icey |    4 |

|  6 | s    | NULL |

|  7 | rrrr |    4 |

|  8 | xx   |    3 |

|  9 | ddd  |    4 |

+----+------+------+

9 rows in set (0.00 sec)

root@test 04:23:49>insert into xbb values(20,'fdfd',12),(30,'sdas',1);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

root@test 04:24:46>commit;

Query OK, 0 rows affected (0.05 sec)

root@test 04:24:47>delete from xbb where id = 25;

Query OK, 0 rows affected (0.00 sec)

root@test 04:25:07>insert into xbb values(25,'sds',123);

Query OK, 1 row affected (0.00 sec)

事务二：

root@test 04:24:54>delete from xbb where id=27;

Query OK, 0 rows affected (0.00 sec)

root@test 04:27:54>insert into xbb values(27,'eew',43);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

而此时查看锁情况：

---TRANSACTION 263075, ACTIVE 223 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)

MySQL thread id 94, OS thread handle 0x7fe24e060700, query id 1272 localhost root update

insert into xbb values(27,'eew',43)

Trx read view will not see trx with id >= 263076, sees < 263071

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 65 page no 3 n bits 88 index `PRIMARY` of table `test`.`xbb` trx id 263075  lock_mode X locks gap before rec insert intention waiting

------------------

从实验中可以得知，

间隙锁的出现主要集中在同一个事务中先delete 后 insert的情况下， 当我们通过一个参数去删除一条记录的时候， 如果参数在数据库中存在， 那么这个时候产生的是普通行锁， 锁住这个记录， 然后删除， 然后释放锁。如果这条记录不存在，问题就来了， 数据库会扫描索引，发现这个记录不存在， 这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个比给定参数大的值， 然后以此为界，构建一个区间， 锁住整个区间内的数据， 一个特别容易出现死锁的间隙锁诞生了。