mysql行锁和表锁

参考：https://www.cnblogs.com/itdragon/p/8194622.html

InnoDB与MyISAM区别

InnoDB支持事务
InnoDB支持行锁

行锁

概念：

行锁的劣势：开销大，加锁慢，会出现死锁

行锁的优势：锁粒度小，锁冲突的概率第，处理并发的能力强

加锁的方式：自动加锁。对于update、delete和insert语句，InnoDB会自动给涉及到的数据集加排他锁；对于普通的select语句，InnoDB不会加任何锁；我们可以显示的额手动加锁；

手动加锁：

共享锁：select * from tablename where id=1 in share mode;

排他锁：select * from tablename where id = 1 for update;

实验：

实验一

说明:

对于update、delete和insert语句，InnoDB会自动给涉及到的数据集加排他锁；对于普通的select语句，InnoDB不会加任何锁；我们可以显示的额手动加锁；

序号	事务A	事务B
1	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
2	mysql> select * from user; 1 row in set (0.00 sec)	mysql> select * from user; 1 row in set (0.00 sec)
3	mysql> update user set name = 'xtt' where id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
4		mysql> select * from user; 1 row in set (0.00 sec)
5		mysql> update user set name = 'niufuren' where id = 2;#阻塞
6	mysql> commit; Query OK, 0 rows affected (0.00 sec)
7		Query OK, 1 row affected (17.37 sec) Rows matched: 1 Changed: 1 Warnings: 0
8		mysql> commit; Query OK, 0 rows affected (0.00 sec)

结论：

对于update、delete、insert引擎InnoDB会自动加上排他锁，后更新的事务会被阻塞，直到上一个事务提交操作。同时，在事务B中第4步是可以正常查询的，当然这里是MVCC的原理了。

实验二

说明：

select的排他锁和共享锁的显示加锁操作

序号	事务A	事务B
1	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
2	mysql> select * from user where id = 2 lock in share mode; 1 row in set (0.00 sec)
3		select * from user where id = 2; 1 row in set (0.00 sec)
4		select * from user where id = 2 lock in share mode; 1 row in set (0.00 sec)
5		mysql>update user set name = 'tt' where id = 2;#阻塞
6	mysql>update user set name = 'tt' where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
7	mysql> commit; Query OK, 0 rows affected (0.00 sec)	mysql> commit; Query OK, 0 rows affected (0.00 sec)

	mysql> select * from user where id = 2 for update; 1 row in set (23.19 sec)
		update user set name = 'xtt' where id = 2; ^CC -- query aborted ERROR 1317 (70100): Query execution was interrupted
		mysql> select * from user where id = 2 for update; ^CC -- query aborted ERROR 1317 (70100): Query execution was interrupted
		mysql> select * from user where id = 2 lock in share mode; ^CC -- query aborted ERROR 1317 (70100): Query execution was interrupted
	mysql> commit; Query OK, 0 rows affected (0.00 sec)	mysql> commit; Query OK, 0 rows affected (0.00 sec)

结论：

select的共享锁lock in share mode,我们可以理解为读锁，其他事务允许读取，但是不能进行更新操作；
从事务B的第4步可以看出共享锁语句可以在不同的事务中执行，但是只有第一个执行共享锁语句的的事务才拥有该行锁；
从事务B第5、6步可以看出，在共享锁下，如果其他事务进行更新操作会被阻塞直到事务A执行commit操作，如果事务A也行了更新操作，那么事务B的更新阻塞语句将报错；
select的排他锁for update;可以理解为写锁，其他事务同样不能进行更新操作，虽然同样可以select操作，但是其他事务不能再对该数据加共享锁和排他锁了；

ps：我们可以细想一下，对于update、delete、insert语句InnoDB都是自动加排他锁，那么如果想给seleect也加锁怎么办呢？InnoDB因此提供了基于select的两种显示锁。

实验三

说明

当查询没有使用到索引的时候，注意了，这时候行锁会升级为表锁

序号	事务A	事务B
1	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
2	mysql> select * from user where name = 'fc' for update; 2 rows in set (0.00 sec)
3		mysql> select * from user where name = 'tt' for update; #阻塞
4	mysql> commit; Query OK, 0 rows affected (0.00 sec)
5		1 row in set (14.74 sec)
6		mysql> commit; Query OK, 0 rows affected (0.00 sec)

结论：

通过事务B的3、4,可以发现这里并不仅仅是锁住了第一条数据，而是将整个表加了锁。这是有与InnoDB有自己的执行计划，当mysql认为对行加锁有可能会造成大量的死锁，大量的行锁有可能会造成性能急剧下降时，行锁会升级为表锁。

间隙锁

当我们使用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录索引项加锁（注意这里如果没有使用索引的话，其实会变成全表扫描），对于键值范围内但是不存在数据范围的记录，我们叫间隙，InnoDB也会对这个间隙加锁，这种锁机制就是间隙锁（Next-Key锁）

#事务A
mysql> update user set name = concat('x' , name) where id > 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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


#事务B
mysql> update user set name = 'xfc' where id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> insert into user values(4,'zhubajie');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from user where id = 4;
Query OK, 0 rows affected (0.00 sec)

结论：

通过上面的实验，我们可以看出，当使用范围锁时，会对范围内的已有记录和不存在记录都加锁，但是对于不存在记录其他事务是可以进行更新和删除操作的（因为update是实时查，虽然其他事务可以删除和修改不存在数据，但是这对于事务A的update不会产生影响），但是不允许其他事务执行新增操作，因为这里会对事务A的更新操作产生影响

危害:

当范围条件过大时，则InnoDB会对范围的所有键值全部锁定，很容易造成性能问题

分析行锁

通过检查InnoDB_row_lock状态变量分析系统上的行锁竞争情况show status like 'innodb_row_lock%'

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 399445 |
| Innodb_row_lock_time_avg      | 23496  |
| Innodb_row_lock_time_max      | 51124  |
| Innodb_row_lock_waits         | 17     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

innodb_row_lock_current_waits: 当前正在等待锁定的数量
innodb_row_lock_time: 从系统启动到现在锁定总时间长度；非常重要的参数，
innodb_row_lock_time_avg: 每次等待所花平均时间；非常重要的参数，
innodb_row_lock_time_max: 从系统启动到现在等待最常的一次所花的时间；
innodb_row_lock_waits: 系统启动后到现在总共等待的次数；非常重要的参数。直接决定优化的方向和策略。

行锁的优化

尽可能让所有的数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁；
尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围；
尽可能减少事务的粒度，比如控制事务的大小，从而减少锁定资源量和锁定时间长度，从而减少锁竞争，提供性能；
尽可能使用低级别的事务隔离，隔离界别越高，并发处理能力越低；

表锁

概念：

表锁的优势：开销小，加锁快，无死锁

表锁的劣势：锁粒度大，发生锁冲突概率高，并发处理能力低

加锁方式：自动加锁。查询操操作，会自动给涉及的所有表加读锁，更新操作会自动给涉及的表加写锁，当然也可以显示加锁；

显示加锁：

共享读锁：lock table tablename read;

独占写锁：lock table tablename write;

批量解锁：unlock tabes;

实验：

实验一

说明：读锁

查询操操作，会自动给涉及的所有表加读锁，更新操作会自动给涉及的表加写锁，当然也可以显示加锁；

序号	事务A	事务B
	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
	mysql> lock table user read; Query OK, 0 rows affected (22.70 sec)
		mysql> lock table user read; Query OK, 0 rows affected (22.70 sec)
	mysql> select * from user; 3 rows in set (0.00 sec)	mysql> select * from user; 3 rows in set (0.00 sec)
	mysql> update user set name = 'xtt' where id = 2; ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated	mysql> update user set name = 'xtt' where id = 2; ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
		mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
		mysql> update user set name = 'tt' where id = 2;
	mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
		Query OK, 1 row affected (1 min 16.04 sec) Rows matched: 1 Changed: 1 Warnings: 0
	mysql> commit; Query OK, 0 rows affected (0.00 sec)	mysql> commit; Query OK, 0 rows affected (0.00 sec)

结论：其实给表加锁是可以重复加的，解锁只有由加锁的用户解锁

实验二

说明：写锁

更新操作会自动给涉及的表加写锁，其他事务所有操作都需要等待

序号	事务A	事务B
	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
	mysql> lock table user write; Query OK, 0 rows affected (22.70 sec)
		mysql> select * from user; #阻塞
	mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
	mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
		3 rows in set (6.13 sec)
	mysql> commit; Query OK, 0 rows affected (0.00 sec)	mysql> commit; Query OK, 0 rows affected (0.00 sec)

结论：

表写锁，当前事务是可以进行读写操作的，但是不能进行DDL操作。其他事务是不能进行任何查询和更新操作的

查看加锁情况

show open tables:1表示加锁，0表示未加锁

mysql> show open tables where in_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| xiyouji  | user  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

分析表锁定

show status like 'table_locks%'

mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 107   |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

table_locks_immediate: 表示立即释放表锁数。
table_locks_waited: 表示需要等待的表锁数。此值越高则说明存在着越严重的表级锁争用情况。

需要使用表锁的场景

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

总结

1 InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。
2 InnoDB 自动给修改操作加锁，给查询操作不自动加锁
3 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。
4 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。
5 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。
6 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。

posted @ 2020-05-21 08:48 蒙多~想去哪就去哪阅读(138) 评论(0) 收藏举报

刷新页面返回顶部

雕琢时光