MySQL锁知识(一)【Slikeの后花园】

MySQL锁知识(一)

发表于 2021-08-02 | 更新于 2021-08-06

数据库

MySQL锁知识

MySQL锁知识(一)

前言

MySQL数据库锁机制的认识

本次记录的是Mysql数据中锁的一方面知识，主要从数据库锁类型->各种类型的详细讲解->关于事务的一些语法

锁的类型

首先是锁的区分：

1.按照锁的粒度来划分：全局锁、表锁、行锁、页锁

2.思想上的锁划分：悲观锁、乐观锁。

3.InnoDB中有几种行级锁类型：

共享/排它锁(Shared and Exclusive Locks)
意向锁(Intention Locks)
自增锁(Auto-inc Locks)
记录锁(Record Locks)
间隙锁(Gap Locks)
临键锁(Next-key Locks)
插入意向锁(Insert Intention Locks)

表锁：意向锁(IS锁、IX锁)、自增锁；

行锁：记录锁、间隙锁、临键锁、插入意向锁；

锁的详解

全局锁

全局锁也就是给整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是：Flush tables with read lock。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。一般都是用在数据库联机备份。而解锁的语句是：unlock tables

flush tables with read lock 也就是将所有的脏页都要刷新到磁盘，然后对所有的表加上了读锁，于是这时候直接拷贝数据文件也就是安全的。但是如果你发出命令flush tables with read lock时，还有其他的操作，而起是很耗时的操作呢？先说写操作，这个FTWRL肯定是得等的，等写操作完成才能执行FTWRL

既然要全库只读，为什么不使用 set global readonly=true 的方式？
- 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大
- 在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险比较高
表锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

表级锁的语法：LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

解锁: unlock table table_name /unlock tables table_names 每次只能持一个锁

这个命令是表级别的锁定，可以定制锁定某一个表，{READ [LOCAL] | [LOW_PRIORITY] WRITE}则是选择项READ表示只读，而[LOW_PRIORITY] WRITE表示(低优先)降级写入的意思。例如：lock tables test read; 不影响其他表的写操作。

如果在某个线程A中执行lock tables t1 read,t2 wirte;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许

另一类表级的锁是MDL。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行

在MySQL5.5版本引入了MDL(Metadata lock，元数据锁)，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁

读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，需要特别小心，以免对线上服务造成影响

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。

行锁就是针对数据表中行记录的锁。比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新
- 两阶段锁协议
  
  首先事务A和事务B都在修改同一行数据，事务A持有的两个记录的行锁都是在commit的时候才释放的，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。
  
  在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。　　
  
  如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，这样在上锁时才能最大可能度的减少死锁的发生。
- 死锁和死锁检测
  
  在并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
  
  例如：事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种解决策略：
  
  1.直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置
  
  2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑
  
  在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的
  
  正常情况下还是要采用主动死锁检查策略，而且innodb_deadlock_detect的默认值本身就是on。主动死锁监测在发生死锁的时候，是能够快速发现并进行处理的，但是它有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。如果所有事务都要更新同一行的场景，每个新来的被堵住的线程都要判断会不会由于自己的加入导致死锁，这是一个时间复杂度是O(n)的操作
页锁

表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
乐观锁和悲观锁、共享锁和排它锁

乐观锁和悲观锁只是意义上的锁，需要自己通过代码的方式去实现。共享锁和排它锁顾名思义就多个线程可以共同获取和只能一个线程获取的锁。

乐观锁：指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。会出现数据被临时串改的问题也就是ABA问题，

ABA：线程1在通过CAS乐观锁获取数据时：数据为A，此时A数据又被线程2修改成了B，后又被线程3修改回了A，而后线程1修改数据时，虽然还是A，但已经不是初始条件的A了，中间发生了A变B，B又变A的变化，此A已经非彼A，数据却成功修改，可能导致错误

悲观锁：数据在进行操作之前就会判断是都冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

共享锁：数据库的增删改操作默认都会加排他锁，而查询不会加任何锁
共享锁又称读锁，是针对多个事务的，对某一资源加共享锁，自身可以读取数据，其他事务也可以读取数据（但先要获取该数据的共享锁），上共享锁的数据无法修改。要想修改就必须等所有共享锁都释放完之后，
语法：SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁：排它锁又称写锁，也是针对多个事务的，但是他对于同一资源只有一个锁，获取到排它锁的事务，既可以修改数据也可以查询数据，但是其他事务就不能对上了排它锁的数据进行任何操作。
语法：SELECT ... FOR UPDATE;5

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

关于锁的一些语法

#当前运行的所有事务
select * from information_schema.innodb_trx;  

#当前出现的锁
select * from information_schema.innodb_locks;

#锁等待的对应关系
select * from information_schema.innodb_lock_waits;

# 查看下autocommit的值
select @@autocommit;

# 查看数据库的隔离级别：
select @@tx_isolation;

# 查看先当前库的线程情况：
show full processlist;

# kill 线程
kill 1825;

# 查看表是什么时候更新的
SELECT * FROM information_schema.tables WHERE table_schema='test1' AND table_name='rpt_test' ;

#整个数据库备份
mysqldump -u root -p --databases wxss  > C:\Users\Administrator\Desktop\sql\wxss_database.sql

本文作者: Slike-V

本文链接: http://Slike-v.github.io/2021/08/02/MySQL锁知识/