Mysql数据库锁详解以及查询

Mysql数据库官网

https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-locks-table.html

数据库锁查询

查询未提交的事务
select * from information_schema.innodb_trx
查询正在锁的事务
select * from information_schema.innodb_locks performance_schema.data_locks;
查询等待锁的事务
select * from information_schema.innodb_lock_waits performance_schema.data_lock_waits ;
innodb_locks表在8.0.1版本及以后后由performance_schema.data_locks表所代替，innodb_lock_waits表则由performance_schema.data_lock_waits表代替。

为什么要有锁？

加锁是数据库管理系统（DBMS）中的并发控制任务时确保在多个事务同时存取数据时不破坏事务的隔离性和统一性以及数据库的统一性的一个非常重要的技术。

MYSQL数据库锁按照不同类型的分类也有多种

1按照锁的思想划分为：乐观锁和悲观锁
2按照锁的使用方式可以分为：共享锁、排他锁（悲观锁的一种实现）
3按照锁的力度分为：表级锁、页级锁、行级锁
4InnoDB中有几种行级锁类型:
Record Lock（索引锁）
Gap Lock（间隙锁）
Next-Key Lock:Record Lock +Gap Lock

思想划分：乐观锁和悲观锁

乐观并发控制和悲观并发控制时候并发控制的主要采用的技术手段
无论是乐观锁还是悲观锁都是人们定义出来的一种思想。

悲观锁

正如其名，悲观锁指的对数据被外界修改采取悲观保守态度，因此整个数据处理过程，将数据处于锁定状态，悲观锁的实现往往依靠数据库提供的锁机制（其实也只有数据库提供的锁机制才能真正保证数据访问的排他性，否者，即使本系统中实现了加锁机制，也无法保证外部的系统不会修改数据）

乐观锁

对数据被外界修改采取乐观态度，只有在数据要提交更新的时候才对原数据进行冲突与否的检测，如果冲突了，那么返回错误信息让用户决定如何处理。
乐观锁是通过代码实现的，常用方式是对数据库表加一个version，每次操作对version加1。
当你要操作数据的时候，获取数据，调整数据，根据数据和版很号更新原数据，如果原数据版本号对应不上了，那么就更新失败，重试或者抛出异常。
使用方式：共享锁和排他锁

共享锁（S所）

共享锁又称为读锁，是读取操作创建的锁。存在此锁是其他用户也可以正常的读取数据，但是不能对数据进行修改，直到所有的共享锁被释放。
如果一个事务给数据加上共享锁之后，其他事务只能再对此数据加共享锁，不嫩加排他锁，获取共享锁的事务，只能读取数据，不能修改数据。
用法：select … lock in share mode;

排他锁（X锁）

排他锁又称为写锁，如果有事务对a数据加上排他锁之后，其他事务不允许在对a加任何类型的封锁，获得排他锁的事务既能对数据进行读取，又能对数据进行修改。
如果没有其他事务对数据加排他锁时，事务可以成功申请到排他锁，否则会被阻塞。
用法：select … for update;

力度划分：表锁、页锁、行锁

MyISAM和MEMORY存储引擎采用的是表级锁，
页级锁仅被BDB存储引擎支持，
InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。

表锁

表锁是mysql粒度最大的一种锁，表示当前操作对整张表加锁，资源开销比行锁少，而且不会发生死锁情况，但是发生冲突的概率特别大，被大部分mysql引擎支持
表锁由mysql server实现，一般在执行DDL语句时会对整个表进行加锁，比如说ALTER TABLE等操作。表级锁是一次性锁技术，在回话开始的时候一次性把所有用到的表都加上锁，在表锁释放前，只能访问这些表的数据，不能访问其他表的数据，直到最后通过unlock tables释放所有的表锁。并且，如果回话在给表加锁的时候会释放之前持有的所有锁；会话持有其他表锁执行start transaction 或者begin开启事务时，也会释放之前持有的锁。
共享锁用法:lock table table_name[as alias_name] read;
排他锁用法:lock table table_name[as alias_name][low_priority]write
解锁:unlock tables;

页锁

粒度介于行锁和表锁之间，表锁速度快但是冲突多，行锁速度慢但是冲突少，页锁就是一种折中的方式，一次锁定相邻的一组记录。Mysql页级锁仅被BDB存储引擎支持

行锁

粒度最小，所以数据库消耗也最大。一次锁定某一行记录。

记录锁(Record Lock)

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。
记录锁的作用：加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

间隙锁（Gap Lock）

锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

临键锁Next-Key Lock

行锁和间隙锁组合起来就叫Next-Key Lock。

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

自增锁(Auto-inc Locks)

AUTO-INC LOCKS是一种表级别锁，当一个表格中存在AUTO_INCREMENT列，事务尝试在该表中插入记录时，会使用该中特殊的表级别锁。最简单的场景是，当A事务试图在该表中插入记录时，其他试图在该表中插入记录的事务必须等待A事务，以使A事务插入的记录获得连续的主键。