锁分共享锁(读锁)&排他锁(写锁)
锁粒度分表锁&行锁
表锁为最基本锁策略,也是开销最小的策略。
行锁可以最大程度支持并发处理,同时也带来最大的锁开销。
以下是我对锁做的一些测试:
隔离级别: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语句获取到的就是一个间隙锁,然后数据库会向左扫描扫到第一个比给定参数小的值, 向右扫描扫描到第一个比给定参数大的值, 然后以此为界,构建一个区间, 锁住整个区间内的数据, 一个特别容易出现死锁的间隙锁诞生了。