MYSQL锁

一. 查询,修改,删除,插入的时候会不会主动加锁?

在读已提交的事务隔离级别下

对于Update、Delete和Insert语句，InnoDB会自动给涉及数据集加排他锁（X)。

对于普通SELECT语句，InnoDB不会加任何锁。

 

二. 查询,修改,删除,插入的时候是否需要等待锁释放?

获取的结果集上有锁则需要等待锁。

 

三. 为什么需要锁

1. 需要保证数据的一致性,避免，脏读，幻读，不可重复读

脏读: 读到事务回滚前的内容

幻读: 根据范围查询，一次事务内两次查询获得的结果条数不一致(被另外一个事务添加或者删除)

不可重复读: 一次事务内同一条记录读到的值不一致(被另一个事务修改)

 

四. 为什么需要了解锁

锁等待的时候会导致数据库的性能下降, 数据库操作(查询，更新）受阻塞，甚至发生死锁

 

 五. MYSQL怎么实现锁

略

 

六. 为什么会发生死锁

1. 互相等待释放

2. 长时间不释放锁

 

七. 怎么检测死锁

1.#查看当前正在被锁的事务（锁请求超时后则查不到）

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

2.#查看当前等待锁的事务（锁请求超时后则查不到）

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

3.#查看当前未提交的事务（如果死锁等待超时,事务可能还没有关闭）

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

4.#查看正在被访问的表

mysql> show OPEN TABLES where In_use > 0;

5.#查看最近一个死锁情况

mysql> SHOW ENGINE INNODB STATUS\G

 

八. 如何避免死锁

1.最小化事务范围

2.按照相同的顺序加锁

3.为表设计合适的索引，如果没有合适的索引，可能会锁定表里的每一行记录

 

九. 引擎和锁

InnoDB

1. 用户可使用的锁

共享锁(S): 允许一个事务读, 其他事务无法获取到相同的数据集。容易发生死锁。(in share mode)

- 可以在低级别的事务隔离界别下，保证可重复读

排他锁(X): 允许一个事务写, 其他事务无法获取到相同数据集的共享锁和排他锁。(for update)

- 间隙锁: 锁定一个范围段。必须是RR隔离级别，必须有索引

- next-key: 锁定相邻的记录。默认是next-key锁(=间隙锁+记录锁)

 

2. 内部锁

意向共享锁(IS): 加S之前，先需要获得S

意向排他锁(IX): 加X之前，先需要获得IX

 

十. 锁定的范围

行锁: 锁定的是索引项，如果没有对应的索引项，将使用表锁

表锁: 锁定整个表

 

十一. 为什么需要意向排他锁和意向共享锁?

优化检测锁的效率, 对行加锁的时候自动加上意向表锁，其他事务要对该表加表锁时，会被阻塞

 

十二. 隔离级别和锁

隔离级别, 一致性读和锁, SQL		Read Uncommited	Read Commited	Repeatable Read	Serializable
SQL	条件
select	相等	None locks	Consisten read/None lock	Consisten read/None lock	Share locks
	范围	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
update	相等	exclusive locks	exclusive locks	exclusive locks	Exclusive locks
	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Insert	N/A	exclusive locks	exclusive locks	exclusive locks	exclusive locks
replace	无键冲突	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	键冲突	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
delete	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Select ... from ... Lock in share mode	相等	Share locks	Share locks	Share locks	Share locks
	范围	Share locks	Share locks	Share Next-Key	Share Next-Key
Select * from ... For update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks	Share locks	exclusive next-key	exclusive next-key
Insert into ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
create table ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key

 

 

参考资料

https://blog.csdn.net/kk185800961/article/details/79528841

http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html