SQL锁机制
作者: 西魏陶渊明 博客: https://blog.springlearn.cn/
!!! tip 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 !!!
一、场景模拟
1create table shop
2(
3 id int(4) primary key default 0,
4 name varchar(20) default '衣服',
5 status varchar(20) default '可售'
6)engine = innodb default charset=utf8;
7
8insert into shop values(1,'衣服','可售');1mysql> select * from shop;
2+----+--------+--------+
3| id | name | status |
4+----+--------+--------+
5| 1 | 衣服 | 可售 |
6+----+--------+--------+
71 row in set (0.00 sec)有一个X姨夫,两个用户并发操作问题
A先看到衣服: X加锁 -> 试衣服 -> 下单 -> 付款 -> 打包 -> X解锁 B也相对衣服: 发现X已经被A加锁了,等待X解锁。
二、锁知识
锁类型
a. 读锁(共享锁): b. 写锁(互斥锁): 如果当前写操作没有完毕,则无法进行其他的写操作。
锁范围
- innodb默认行锁(开销大,加锁慢,锁范围小,易死锁,不容器锁冲突,并发度高)
- MyISAM默认表锁(开销小,加锁块,无死锁,但是锁范围大容器锁冲突,并发度低)
- 表锁(对一张表整体加锁)
- 行锁(对一行数据进行加锁)
三、锁分析
1. 查看加锁的表
show open tables;
1代表加锁
1mysql> show open tables;
2+--------------------+---------------------------+--------+-------------+
3| Database | Table | In_use | Name_locked |
4+--------------------+---------------------------+--------+-------------+
5| test | emp | 0 | 0 |
6| test | test_innodb_lock | 0 | 0 |
7| test | test03 | 0 | 0 |
8| test | teacher2 | 0 | 0 |
9| test | course2 | 0 | 0 |
10| test | book | 0 | 0 |
11| test | shop | 1 | 0 |
12| test | staffs | 0 | 0 |
13| test | dept | 0 | 0 |
14+--------------------+---------------------------+--------+-------------+
1573 rows in set (0.00 sec)2. 查看锁的严重程度
show status like '%Table_locks%';
1mysql> show status like '%Table_locks%';
2+-----------------------+-------+
3| Variable_name | Value |
4+-----------------------+-------+
5| Table_locks_immediate | 79 |
6| Table_locks_waited | 0 |
7+-----------------------+-------+
82 rows in set (0.00 sec)Table_locks_immediate 能立马加锁 Table_locks_waited 越大说明竞争越大
建议: Table_locks_immediate/Table_locks_waited > 5000,建议采用innodb,否则建议MyISAM。
四、模拟加表锁
lock table 表1 read/write,表2 read/write
1lock table shop write;加表读锁
如A会话,对shop表加了read锁,则该会话可以对shop表进行读操作,不能进行写操作。 并且只能读自己加锁了的表,如下面列子最shop加锁,能读shop不能写shop,不能读test03
如果对shop表加了read锁,那么只能对shop进行读,其他任何操作都不行了
1mysql> lock table shop read;
2Query OK, 0 rows affected (0.01 sec)
3
4mysql> select * from shop
5 -> ;
6+----+--------+-----------+
7| id | name | status |
8+----+--------+-----------+
9| 1 | 衣服 | 已占用 |
10+----+--------+-----------+
111 row in set (0.00 sec)
12
13mysql> update shop set status = '可售' where id = 1;
14ERROR 1099 (HY000): Table 'shop' was locked with a READ lock and can't be updated
15mysql> select * from test03;
16ERROR 1100 (HY000): Table 'test03' was not locked with LOCK TABLES
其他B会话中,对于shop表能读不能写,但是不影响操作其他表。
加表写锁
- 会话A: lock table shop write; 当前会话可以对加了锁的表进行任意操作;但是不能操作其他表。
- 其他会话B: 当会话A释放了锁,B才能对这个表进行增删改查;
五、模拟加行锁
1create table linelock
2(
3 id int(5) primary key auto_increment,
4 name varchar(20)
5) engine = innodb;
6
7insert into linelock(name) values('1');
8insert into linelock(name) values('2');
9insert into linelock(name) values('3');
10insert into linelock(name) values('4');
11insert into linelock(name) values('5');A窗口写操作
insert into linelock(name) values('6');
此时B窗口执行update更新会被锁定。
当A会话commit之后B就能继续操作了。
行锁是通过事务进行解锁的。
行锁转表锁
如果索引列进行了类型转换,则索引失效。
A窗口执行
update linelock set name = 'ai' where name = 3;
B窗口执行
update linelock set name = 'ax' where name = 4;
间隙锁
update linelock set name = 'x' where id > 1 and id < 8;
行锁分析
show status like '%innodb_row_lock%';
1mysql> show status like '%innodb_row_lock%';
2+-------------------------------+--------+
3| Variable_name | Value |
4+-------------------------------+--------+
5| Innodb_row_lock_current_waits | 1 |
6| Innodb_row_lock_time | 207248 |
7| Innodb_row_lock_time_avg | 34541 |
8| Innodb_row_lock_time_max | 51605 |
9| Innodb_row_lock_waits | 6 |
10+-------------------------------+--------+
115 rows in set (0.00 sec)
| Variable_name | Value |
|---|---|
| Innodb_row_lock_current_waits | 当前正在等待的锁的数量 |
| Innodb_row_lock_time | 等待总时长,从系统启动到现在一共等待时间 |
| Innodb_row_lock_time_avg | 平均等待时长 |
| Innodb_row_lock_time_max | 最大等待时长 |
| Innodb_row_lock_waits | 等待的次数 |
查询语句加锁
set autocommit=0;
select * from linelock for update;
最后求关注,求订阅,谢谢你的阅读!