锁
锁是计算机协调多个进程或线程并发访问某一资源的机制
在数据库中,除传统的统计资源(如 CPU,RAM,I/O等)的争用以外,数据也是一种供许多用户共享的资源,如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,所冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要。也更加复杂
锁的分类
- 从对数据的操作的类型: 读 \ 写
- 读锁: 共享锁,针对同一份数据,多个读操作可以同时进行而不会互相影响
- 写锁: 当前写操作没有完成,它会阻断其他写锁和读锁
- 从对数据操作的粒度分: 行 \ 表
表锁(偏读)MyISAM
偏向MyISAM 存储引擎,开销小,加锁快,无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
创建一个表格
CREATE TABLE mylock(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(6)
)ENGINE MYISAM;
# 插入数据
insert into mylock(name) value ("a");
insert into mylock(name) value ("b");
insert into mylock(name) value ("c");
insert into mylock(name) value ("d");
insert into mylock(name) value ("e");
查看表格是否加过锁
SHOW OPEN tables;
show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sakila | sales_by_store | 0 | 0 |
| dy | jc_shop_product_sxhq_history | 0 | 0 |
| sakila | staff | 0 | 0 |
# 因为太多了所以就放出来一部分, 值为0 说明没有锁
如何加上一把读锁 和 写锁
lock table mylock read,staffs write;
Query OK, 0 rows affected (0.01 sec)
# 查看是否已经加上锁
| db0629 | staffs | 1 | 0 |
| db0629 | mylock | 1 | 0 |
# 解锁
lock tables;
先给我们刚才的那个创建的表格添加上读锁
lock table mylock read;
# 查看数据
select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.01 sec)
在重新打开命令行连接数据库再次查询该表格
mysql -uroot -proot
# 查询数据
select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.01 sec)
因为读锁是共享锁,所以查询没有问题。
通过第一个连接尝试进行更新数据
update mylock set name = "a2" where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
这里发现无法进行写操作。
通过第一个连接尝试进行其他表的读取
select * from dept;
ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES
通过第二个连接进行修改mylock表格
mysql> update mylock set name = "a2" where id = 1;
回车点击修改之后,会一直等待,处于阻塞状态。
通过第一个连接进行解锁,第二个连接就会执行完毕。
unlock tables;
Query OK, 0 rows affected (0.00 sec)
update mylock set name = "a2" where id = 1;
Query OK, 1 row affected (53.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以看到执行时间 53.01 sec
连接一锁(读)表,连接二 正常
连接一 | 连接二 | |
---|---|---|
是否可以查询被锁的表 | √ | √ |
是否可以查询其他的表 | × | √ |
是否可以更新被锁表 | × | √,需要等待锁释放 |
是否可以更新其他表 | × | √ |
总结
当前连接加了表(读)锁之后,你只可以读取当前被锁的表,无法读取其他的表,无法更新本表,无法更新其他表。其他连接可以读取当前的表,但是更新数据的时候,需要等待索的释放,获得到锁,更新操作才能完成。
表锁 (写锁)
添加写锁
lock table mylock write;
Query OK, 0 rows affected (0.00 sec)
当前连接查询被锁的表
select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
可以读取
当前连接更新被锁的表
update mylock set name = "b2" where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以更新
当前连接尝试是否可以查看其他的表
select * from dept;
ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES
无法读取其他表
其他连接是否可以查询被锁的表
select * from mylock;
需要等待锁释放,阻塞状态中
连接一进行锁释放
unlock tables;
Query OK, 0 rows affected (0.00 sec)
select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b2 |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (42.20 sec)
执行时间 42.20 sec
连接二是否可以进行读取其他表
select * from dept;
+----+--------+------------+----------+
| id | deptno | dname | loc |
+----+--------+------------+----------+
| 1 | 101 | IOxBEXZPDA | hkHqIsuB |
| 2 | 102 | xyzRSXkBFV | vjjYtTPA |
| 3 | 103 | vtibGTEeEU | bcOyWhNX |
| 4 | 104 | RirCkNGeCc | jzXkeaLO |
| 5 | 105 | FHPwiUPWRh | GqSTGyoW |
| 6 | 106 | yqOkIflwrf | yApJEjCY |
| 7 | 107 | fQGlmjoKAw | qMXWBpqE |
| 8 | 108 | OAQnKFgyPu | YUgECCLn |
| 9 | 109 | LKYRNarSvw | GLCwxXjL |
| 10 | 110 | koFjhRVCJz | lPhQIOzV |
+----+--------+------------+----------+
没有问题
连接2 是否可以更新被锁的表
update mylock set name = "c2" where id = 3;
阻塞,需要等待所释放
unlock tables;
Query OK, 0 rows affected (0.00 sec)
update mylock set name = "c2" where id = 3;
Query OK, 1 row affected (1 min 28.68 sec)
Rows matched: 1 Changed: 1 Warnings: 0
连接一释放锁之后,连接2 获得锁,更新数据。
连接一锁(写)表,连接二 正常
连接一 | 连接二 | |
---|---|---|
是否可以查询被锁的表 | √ | 阻塞,需要等待所释放 |
是否可以查询其他的表 | × | √ |
是否可以更新被锁表 | × | 阻塞,需要等待锁释放 |
是否可以更新其他表 | × | √ |
总结
当前连接加了表(写)锁之后,你只可以读取当前被锁的表,无法读取其他的表,可以更新本表,无法更新其他表。其他连读取当前的表,的时候会阻塞,等待连接一对锁的释放,才能查询到数据。更新数据的时候,需要等待索的释放,获得到锁,更新操作才能完成。
如何查看表锁定
可以通过检查 table_locks_waited 和 table_locks_immediate 状态标量来分析系统上的表锁定
show status like "table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 120 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
这里两个状态变量记录这 MySQL 内部表级锁定的情况,两个变量说明如下:
Table_locks_immediate
: 产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加 1
Table_locks_waited
: 出现表级锁定争用而发生等待的次数(不能立即获取所得次数,每等待一次锁值加1), 此值较高则说明存在较严重的表级锁争用情况
MyISAM 的读写锁调度是写优先,这也是 MyISAM不适合做写为主表的引擎,因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁。从而造成永远阻塞
行锁
偏向InnoDB存储引擎,开销大,加锁慢;会出现死;锁定粒度小,发生锁冲突的概率比较低,并发度也高
InnoDB 与 MyISAM的最大不同有两点,一是支持事务(TRANSCATION) ; 二是采用了行级锁
创建表格
CREATE TABLE test_innodb_lock(
a int(11),
b varchar(16)
)engine = innodb;
insert into test_innodb_lock values (1,"b2");
insert into test_innodb_lock values (3,"3");
insert into test_innodb_lock values (4,"4000");
insert into test_innodb_lock values (5,"5000");
insert into test_innodb_lock values (6,"6000");
insert into test_innodb_lock values (7,"7000");
insert into test_innodb_lock values (8,"8000");
insert into test_innodb_lock values (9,"9000");
insert into test_innodb_lock values (1,"b1");
create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);
连接一,连接二设置 自动提交为 0
set autocommit = 0;
连接一更新一条数据 a 值 等于 4 的数据
update test_innodb_lock set b ="4001" where a = 4;
# 查询该表中的数据
select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4001 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
连接二 查询数据
select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
发现并没有变化。 这个时候就是解决了脏读问题
连接二更新数据 一条 a 值等于 5 的数据
update test_innodb_lock set b = "5001" where a = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
成功更新
连接二再次更新数据 一条a 值等于 4 的数据
update test_innodb_lock set b = "4002" where a = 4;
# 阻塞。 等待 连接一的锁释放
连接一更新 a 值等于 4 的时候将这一行数据锁住了,其他行并不能直接修改,需要等待 连接 一的 commit 提交事务,释放行锁
连接一 提交事务
commit;
Query OK, 0 rows affected (0.00 sec)
# 连接2 获得到 a = 4 的行锁,更新数据
update test_innodb_lock set b = "4002" where a = 4;
Query OK, 1 row affected (6.26 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这里可以看到 当 提交了 连接一的事务之后, 连接二 获取到 a = 4 的这一行数据的锁,然后进行更新。 链接二在这里阻塞了 6.26 sec。
无索引行锁升级为表锁
索引失效会导致 行锁升级为表锁举个例子
如果连接一更新 a = 4的那条数据,链接二更新 9 的那条数据,根据上面的例子是不会出现阻塞问题的。那么我们现在手动写一个索引失效的sql 让他行锁升级为表锁
# 查看以下该表中建立的索引
SHOW INDEX FROM test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 8 | NULL | NULL | YES | BTREE | | |
| test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
# 查看数据
select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4001 |
| 5 | 5001 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
写一条索引失效的SQL
update test_innodb_lock set a = 41 where b = 4001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查询数据
select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 41 | 4001 |
| 5 | 5001 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
这个时候连接二进行更新 a = 9 的这一行数据
mysql> update test_innodb_lock set b = "9001" where a = 9;
发现更新语句阻塞了
# 连接一提交
commit;
Query OK, 0 rows affected (0.00 sec)
# 连接二 获得锁,更新数据执行时间 44.08sec
update test_innodb_lock set b = "9001" where a = 9;
Query OK, 1 row affected (44.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
commit ;
查询数据
select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 41 | 4001 |
| 5 | 5001 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9001 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
索引失效 会导致 行锁升级为表锁。导致其他更新阻塞。
间隙锁
当我们使用范围条件查询的而不是相等条件检索数据,并请求共享或者排他锁,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做 “间隙(GAP)”;InnoDB也会对这个间隙加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)
举例子:
连接一 更新操作数据
# 范围更新数据, 数据中不存在 a= 2;也就是说并不会操作 a = 2 这条数据
update test_innodb_lock set b = "0629" where a < 2 OR a > 2;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9 Changed: 9 Warnings: 0
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | 0629 |
| 3 | 0629 |
| 41 | 0629 |
| 5 | 0629 |
| 6 | 0629 |
| 7 | 0629 |
| 8 | 0629 |
| 9 | 0629 |
| 1 | 0629 |
+------+------+
连接二更新插入 a = 2 这条数据
insert into test_innodb_lock VALUES (2,"2000");
# 插入 2 数据的时候 阻塞。等待 连接一 事务提交 释放锁
连接一 提交事务
# 提交事务释放锁。
commit;
Query OK, 0 rows affected (0.00 sec)
# 连接二得到锁。开始插入数据, 执行时间 12.38
insert into test_innodb_lock VALUES (2,"2000");
Query OK, 1 row affected (12.38 sec)
间隙锁的危害:
因为QUERY执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值不存在,间隙锁有一个比较致命的弱点,就是当锁定一个范围键值后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的过程中无法插入锁定键值范围内的任何数据。在某些场景下面这可能会对性能造成很大的伤害。
行锁分析
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 | # 当前正在等待锁定的数量
| Innodb_row_lock_time | 163759 | # 从系统启动到现在锁定总时间的长度
| Innodb_row_lock_time_avg | 32751 | # 每次等待所花平均时间
| Innodb_row_lock_time_max | 51025 | # 从系统启动到现在等待最长的一次所化的时间
| Innodb_row_lock_waits | 5 | # 系统启动后到现在总等待的次数
+-------------------------------+--------+
5 rows in set (0.01 sec)
优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能较少检索条件,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可能低级别事务隔离