你知道MySQL锁与事物隔离级别吗？

相关免费学习推荐：mysql数据库（视频）

前言

• MySQL索引底层数据结构与算法
• MySQL性能优化原理-前篇
• MySQL性能优化-实践篇1
• MySQL性能优化-实践篇2

前面我们讲了MySQL数据库底层的数据结构与算法、MySQL性能优化篇一些内容。我们再来聊聊MySQL的锁与事务隔离级别，分上下两篇，本篇重点讲MySQL的行锁与事务隔离级别。

锁定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

• 从性能上分为乐观锁（用版本对比来实现）和 悲观锁；
• 从数据库操作类型分为：读锁 和 写锁 （都属于悲观锁）
  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响；
  • 写锁（排它锁）：当前写操作没有完成之前，它会阻断其它写锁和读锁。
• 从数据库操作的粒度分为：表锁 和 行锁。

对于锁深入的理解，可以查看《关于Java中锁的理解》。

MySQL的锁

• 行锁（Record Locks）

• 间隙锁（Gap Locks）

• 临键锁（Next-key Locks）

• 共享锁/排他锁（Shared and Exclusive Locks）

• 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

• 插入意向锁（Insert Intention Locks）

• 自增锁（Auto-inc Locks）

• 预测锁，这种锁主要用于存储了空间数据的空间索引。

下篇来分别聊聊，本篇重点是行锁以及事务隔离级别。

表锁

每次操作锁住整张表。

• 开销小，加锁快；
• 不会出现死锁；
• 锁粒度大，发生锁冲突的概率最高；
• 并发度最低。

基本操作

示例表，如下：

# 建表SQLCREATE TABLE mylock (    id INT(11) NOT NULL AUTO_INCREMENT,    NAME VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

# 插入数据INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES (&＃39;1&＃39;,&＃39;a&＃39;); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES (&＃39;2&＃39;,&＃39;b&＃39;); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES (&＃39;3&＃39;,&＃39;c&＃39;); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES (&＃39;4&＃39;,&＃39;d&＃39;); 

• 手动增加表锁

lock table 表名称 read(write), 表名称2 read(write); 

• 查看表上加过的锁

show open tables; 

• 删除表锁

unlock tables; 

案例分析 — 加读锁

LOCK TABLE mylock read; 

设置事务隔离级别

set tx_isolation=&＃39;REPEATABLE-READ&＃39;; 

数据库版本是5.7，隔离级别是Repeatable-Read(可重复读)，不同的数据库版本和隔离级别对语句的执行结果影响很大。所以需要说明版本和隔离级别

行锁与隔离级别案例分析

事务控制语句

• BEGIN 或 START TRANSACTION；显式地开启一个事务；
• COMMIT；也可以使用 COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
• ROLLBACK；有可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
• RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier；把事务回滚到标记点；
• SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

事务处理方法

MYSQL 事务处理主要有两种方法：

1. 用 BEGIN, ROLLBACK, COMMIT来实现
   • BEGIN 开始一个事务
   • ROLLBACK 事务回滚
   • COMMIT 事务确认

2. 直接用 SET 来改变 MySQL 的自动提交模式:
   • SET AUTOCOMMIT=0 禁止自动提交
   • SET AUTOCOMMIT=1`` 开启自动提交

示例表，如下：

CREATE TABLE `user` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR (255) DEFAULT NULL,    `balance` INT (11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO `test`.`user` (`name`,`balance`) VALUES (&＃39;zhangsan&＃39;,&＃39;450&＃39;);INSERT INTO `test`.`user` (`name`,`balance`) VALUES (&＃39;lisi&＃39;, &＃39;16000&＃39;);INSERT INTO `test`.`user` (`name`,`balance`) VALUES (&＃39;wangwu&＃39;,&＃39;2400&＃39;); 

行锁演示

一个 session 开启事务更新不提交，另一个 seesion 更新同一条记录会阻塞，更新不同记录u会阻塞。

（7）验证幻读 在客户端A执行 update user set balance = 8888 where id = 4; ，能更新成功，再次查询到客户端B新增的数据。

串行化

（1）打开一个客户端A，并设置当前事务模式为 serializable ，查询表 user 的初始值

set tx_isolation=&＃39;serializable&＃39;; 

对各个状态量的说明如下：

• Innodb_row_lock_current_waits ：当前正在等待锁定的数量
• Innodb_row_lock_time ：从系统启动到现在锁定总时间长度
• Innodb_row_lock_time_avg ：每次等待所花平均时间
• Innodb_row_lock_time_max ：从系统启动到现在等待最长的一次所花时间
• Innodb_row_lock_waits ：系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

• Innodb_row_lock_time_avg （等待平均时长）
• Innodb_row_lock_waits （等待总次数）
• Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统 中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

死锁

set tx_isolation=&＃39;repeatable-read&＃39;; 

Session_1执行：select * from user where id=1 for update;
Session_2执行：select * from user where id=2 for update;
Session_1执行：select * from user where id=2 for update;
Session_2执行：select * from user where id=1 for update; 

查看近期死锁日志信息：

show engine innodb status\G; 

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况 mysql没法自动检测死锁

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁；
2. 合理设计索引，尽量缩小锁的范围；
3. 尽可能减少检索条件范围，避免间隙锁；
4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行；
5. 尽可能低级别事务隔离。

问答

1. MySQL 默认级别是 repeatable-read，有什么办法可以解决幻读妈？

间隙锁（Gap Lock）在某些情况下可以解决幻读问题，它是 Innodb 在 可重复读 提交下为解决幻读问题时引入的锁机制。要避免幻读可以用间隙锁在Session_1 下面执行 update user set name = &＃39;hjh&＃39; where id > 10 and id <= 20; ，则其他 Session 没法在这个范围锁包含的间隙里插入或修改任何数据。

如：user 表有3条数据， id > 2 and id <=3 会把第三条记录锁住，其他会话对则无法对第三条记录做操作。

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

3. 锁定某一行还可以用 local in share mode(共享锁) 和 for update(排它锁) ，例如： select * from test_innodb_lock where a = 2 for update; 这样其他 session 只能读这行数据，修改则会被阻塞，直到锁定行的 session 提交。

以上就是你知道MySQL锁与事物隔离级别吗？的详细内容，更多请关注 第一PHP社区 其它相关文章！