浅析mysql的锁

程序员文章站 2022-06-02 12:59:42

...

目录：
1.锁的定义与分类（表、行、页）
2.锁相关的语句（查看锁）
3.mysql事务
4.乐观锁和悲观锁
5.数据库死锁

1.锁的定义与分类

1.1.锁的定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

1.2.锁的分类

开锁、加锁速度、死锁、粒度、并发性能只能就具体应用的特点来说哪种锁更合适
1)、从对数据操作的类型(读\写)分
　　读锁(共享锁): 针对同一份数据，多个读操作可以同时进行而不会互相影响
　　写锁(排它锁): 当前写操作没有完成前，它会阻断其他写锁和读锁

2)、从对数据操作的粒度分
　　表锁(偏读)
　　行锁(偏写)
　　页锁(了解)

1.2.1.表锁(偏读)

mysql的表级锁有两种模式：
　　表共享读锁(Table Read Lock)
　　表独占写锁(Table Write Lock)
特点：
　　偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
　　MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁
　　读锁会被阻塞写，但不会阻塞写。而写锁则会把读和写都阻塞。
案例分析：
①表锁–读锁

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20)
) engine myisam;  // 这里用MyISAM存储引擎
类似再建一个book表和mylock一样就行；
【手动增加表锁】lock table 表名字 read(write), 表名字2 read(write), 其他;
【释放锁】unlock tables;
【查看表上加过的锁】show open tables;
mysql> lock table mylock read, book write;
mysql> show open tables;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| dbtest   | book  |      1 |           0 |
+----------+-------+--------+-------------+
| dbtest   | mylock|      1 |           0 |
+----------+-------+--------+-------------+
·Database：含有该表的数据库。
·Table：表名称。
·In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
·Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
mysql> update mylock set name='a2' where id=1;
ERROR 1099(HY000): Table 'mylock' was locked with a READ lock and can't be updated
如果是另外一个session2执行上面的update语句，不会报错，sql被阻塞从而一直在等待，直到被锁的session执行unlock tables 才会执行。

②表锁–写锁

session_1	session_2
获取mylock的write锁定mysql>lock tables mylock write;	待Sesson_1开启锁后, session2再连接终端
当前session1对锁定表的查/更新/修改等操作都可以执行	其他session对锁定表的查询被阻塞，需要等待锁被释放；如果没有被阻塞，可能是mysql的缓存，换个查询条件即可
释放锁mysql> unlock tables;	session2获得锁，查询返回(可以看到返回时间有点长)

1.2.2.行锁(偏写)

特点：
　　偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
　　InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁；
　　虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁；

2.锁相关语句

【手动增加表锁】lock table 表名字 read(write), 表名字2 read(write), 其他;
【释放锁】unlock tables;

【查看哪些表被加锁了】mysql> show open tables;

mysql> show open tables;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| dbtest   | book  |      1 |           0 |
+----------+-------+--------+-------------+
| dbtest   | mylock|      1 |           0 |
+----------+-------+--------+-------------+
·Database：含有该表的数据库。
·Table：表名称。
·In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
·Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

【如何分析表锁定】mysql> show status like 'tables%';

mysql> show status like 'tables%';
+------------------------+-------+
| Variable_name          | TABLE |
+------------------------+-------+
| Table_locks_immediate  | 105   |
+------------------------+-------+
| Table_locks_waited     | 1     |
+------------------------+-------+
这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
　a).Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
　b).Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，
     每等待一次锁值加1)，此值高则说明存在着较严重的表级锁定争用情况；

3.mysql 事务

1).事务及其ACID属性
A(Atomicity [ætəmˈɪsɪti])代表原子性，即事务是一个原子操作单元，对数据的修改，要么全部执行，要么一个都不执行;
C(Consistency [kənˈsɪstənsi] )代表一致性，在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构(如B树索引或双向链表)也必须是正确的。
如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。
I(Isolation [aɪsəˈleɪʃn] )代表隔离性，即两个事务不会相互影响、覆盖彼此数据等;
D(Durability [djʊərə'bɪlətɪ])表示持久化，即一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

2).并发事务处理带来的问题
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
a).更新丢失: 当两个或多个事务选择同一行，然后基于选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：最后的更新覆盖了由其他事务所做的更新。
b).脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但尚未提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的。也就是读取了其他事务还没有提交的数据
c).不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了。当前事务已经读取的数据记录，被其他事务修改或删除。
d).幻读: 对于两个事物 T1, T2, T1从一个表中读取了一个字段, 然后T2在该表中新增了一些新的行. 之后, 如果T1再读取同一个表, 就会多出几行。其他事务插入了新的数据，当前事务以相同的查询条件，在那个事务插入数据之前和之后查询数据，得到的数据条数不一样

3).事务隔离级别
隔离级别（isolation level），是指事务与事务之间的隔离程度。
显然，事务隔离程度越高，并发性越差、性能越低；事务隔离程度越低，并发性越强、性能越高。
Mysql 支持4种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
a).read uncommitted(读未提交数据)：允许事务读取未被其他事物提交的变更。脏读、不可重复读和幻读的问题都会出现
b).read commited(读已提交数据)：只允许事务读取已经被其他事务提交的变更。可避免脏读，但不可重复读和幻读问题仍然存在
c).repeatable read(可重复读)：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。可避免脏读和不可重复读，但幻读的问题仍然存在
d).serializable(串行化)：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行增删改操作。所有的并发问题都可以避免，但性能什么低下。

查看当前数据库的事务隔离级别：show variables like 'tx_isolation'

4.乐观锁&悲观锁

悲观锁

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。

优点与不足
悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数。

乐观锁

乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁一般来说有以下2种方式：

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。
Java JUC中的atomic包就是乐观锁的一种实现，AtomicInteger 通过CAS（Compare And Set）操作实现线程安全的自增。

优点与不足
乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

MySQL隐式和显示锁定

MySQL InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB会根据事务隔离级别在需要的时候自动加锁。

另外，InnoDB也支持通过特定的语句进行显示锁定，这些语句不属于SQL规范：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

案例分析（来自网络）：
初始情况：多线程并发情况下，会存在超卖的可能。

/**
     * 更新库存(不考虑并发)
     * @param productId
     * @return
     */
    public boolean updateStockRaw(Long productId){
        ProductStock product = query("SELECT * FROM tb_product_stock WHERE product_id=#{productId}", productId);
        if (product.getNumber() > 0) {
            int updateCnt = update("UPDATE tb_product_stock SET number=number-1 WHERE product_id=#{productId}", productId);
            if(updateCnt > 0){    //更新库存成功
                return true;
            }
        }
        return false;
    }

悲观锁：

/**
     * 更新库存(使用悲观锁)
     * @param productId
     * @return
     */
    public boolean updateStock(Long productId){
        //先锁定商品库存记录
        ProductStock product = query("SELECT * FROM tb_product_stock WHERE product_id=#{productId} FOR UPDATE", productId);
        if (product.getNumber() > 0) {
            int updateCnt = update("UPDATE tb_product_stock SET number=number-1 WHERE product_id=#{productId}", productId);
            if(updateCnt > 0){    //更新库存成功
                return true;
            }
        }
        return false;
    }

乐观锁：使用乐观锁更新库存的时候不加锁，当提交更新时需要判断数据是否已经被修改（AND number=#{number}），只有在 number等于上一次查询到的number时才提交更新。
** 注意** ：
1.UPDATE 语句的WHERE 条件字句上需要建索引；
2.乐观锁的实现，如果ORM使用JPA，springboot中有version注解可以实现；如果ORM使用Mybatis，有插件可以实现；详细的下一篇文章整理。

/**
     * 下单减库存
     * @param productId
     * @return
     */
    public boolean updateStock(Long productId){
        int updateCnt = 0;
        while (updateCnt == 0) {
            ProductStock product = query("SELECT * FROM tb_product_stock WHERE product_id=#{productId}", productId);
            if (product.getNumber() > 0) {
                updateCnt = update("UPDATE tb_product_stock SET number=number-1 WHERE product_id=#{productId} AND number=#{number}", productId, product.getNumber());
                if(updateCnt > 0){    //更新库存成功
                    return true;
                }
            } else {    //卖完啦
                return false;
            }
        }
        return false;
    }

5.死锁

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

5.1.死锁的产生情况

5.1.1死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

这种死锁比较常见，是由于程序的BU*生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

5.1.2死锁的第二种情况

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操作，很容易就出现这种死锁的情况。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

5.1.3死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

5.2.解除正在死锁的状态有两种方法：

第一种：
1.查询是否锁表
show OPEN TABLES where In_use > 0;
2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist
3.杀死进程id（就是上面命令的id列）
kill id

第二种：
1.查看下在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2.杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID

其它关于查看死锁的命令：

1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

5.3查看死锁日志：

SHOW ENGINE INNODB STATUS;

阅读死锁日志
遇到死锁，第一步就是阅读死锁日志。
死锁日志通常分为两部分，上半部分说明了事务1在等待什么锁；
然后日志的下半部分说明了事务2当前持有的锁以及等待的锁；

感谢网友的分享（不全请见谅）：
https://blog.csdn.net/hotdust/article/details/51524469
https://blog.csdn.net/hj7jay/article/details/56274056
……

上一篇：常用数据库查询优化小结

下一篇： oracle 锁表sql查询及解锁

浅析mysql的锁

1.锁的定义与分类

1.1.锁的定义

1.2.锁的分类

1.2.1.表锁(偏读)

1.2.2.行锁(偏写)

2.锁相关语句

3.mysql 事务

4.乐观锁&悲观锁

悲观锁

乐观锁

MySQL隐式和显示锁定

5.死锁

5.1.死锁的产生情况

5.1.1死锁的第一种情况

5.1.2死锁的第二种情况

5.1.3死锁的第三种情况

5.2.解除正在死锁的状态有两种方法：

5.3查看死锁日志：

MySQL InnoDB 日志管理机制中的MTR和日志刷盘

浅析：网站SEO优化站内链接的作用及其优化？

浅析几种提升网站图片优化作用的策略

基于Docker的PHP调用基于Docker的Mysql数据库

Mac上将brew安装的MySql改用Docker执行操作过程

在Docker容器中不需要运行sshd的原因浅析

MySQL数据库存储过程和事务的区别讲解

MySQL开启慢查询日志功能的方法

一次神奇的MySQL死锁排查记录

Mysql数据库的QPS和TPS的意义和计算方法