一文详解-MySQL 事务和锁

当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发起更改请求，为保证数据库记录的更新从一个一致性状态变为另外一个一致性状态，使用事务处理是非常必要的，事务具有以下四个特性：

烟台ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：13518219792（备注：SSL证书合作）期待与您的合作！

MySQL 提供了多种事务型存储引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事务。为了支持事务，InnoDB 存储引擎引入了与事务处理相关的 REDO 日志和 UNDO 日志，同时事务依赖于 MySQL 提供的锁机制

事务执行时需要将执行的事务日志写入日志文件，对应的文件为 REDO 日志。当每条 SQL 进行数据更新操作时，首先将 REDO 日志写进日志缓冲区。当客户端执行 COMMIT 命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式或者时间间隔可以通过参数 innodb_flush_log_at_trx_commit 控制

REDO 日志对应磁盘上的 ib_logifleN 文件，该文件默认为 5MB，建议设置为 512MB，以便容纳较大的事务。MySQL 崩溃恢复时会重新执行 REDO 日志的记录，恢复最新数据，保证已提交事务的持久性

与 REDO 日志相反，UNDO 日志主要用于事务异常时的数据回滚，具体内容就是记录数据被修改前的信息到 UNDO 缓冲区，然后在合适的时间将内容刷新到磁盘

假如由于系统错误或者 rollback 操作而导致事务回滚，可以根据 undo 日志回滚到没修改前的状态，保证未提交事务的原子性

与 REDO 日志不同的是，磁盘上不存在单独的 UNDO 日志文件，所有的 UNDO 日志均存在表空间对应的 .ibd 数据文件中，即使 MySQL 服务启动了独立表空间

在 MySQL 中，可以使用 BEGIN 开始事务，使用 COMMIT 结束事务，中间可以使用 ROLLBACK 回滚事务。MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务

MySQL 定义了四种隔离级别，指定事务中哪些数据改变其他事务可见、哪些数据该表其他事务不可见。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少

InnoDB 系统级事务隔离级别可以使用以下语句设置：

查看系统级事务隔离级别：

InnoDB 会话级事务隔离级别可以使用以下语句设置：

查看会话级事务隔离级别：

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据称为脏读（Dirty Read），即是：首先开启 A 和 B 两个事务，在 B 事务更新但未提交之前，A 事务读取到了更新后的数据，但由于 B 事务回滚，导致 A 事务出现了脏读现象

所有事务只能看见已经提交事务所做的改变，此级别可以解决脏读，但也会导致不可重复读（Nonrepeatable Read）：首先开启 A 和 B 两个事务，A事务读取了 B 事务的数据，在 B 事务更新并提交后，A 事务又读取到了更新后的数据，此时就出现了同一 A 事务中的查询出现了不同的查询结果

MySQL 默认的事务隔离级别，能确保同一事务的多个实例在并发读取数据时看到同样的数据行，理论上会导致一个问题，幻读（Phontom Read）。例如，第一个事务对一个表中的数据做了修改，这种修改会涉及表中的全部数据行，同时第二个事务也修改这个表中的数据，这次的修改是向表中插入一行新数据，此时就会发生操作第一个事务的用户发现表中还有没有修改的数据行

InnoDB 通过多版本并发控制机制（MVCC）解决了该问题：InnoDB 通过为每个数据行增加两个隐含值的方式来实现，这两个隐含值记录了行的创建时间、过期时间以及每一行存储时间发生时的系统版本号，每个查询根据事务的版本号来查询结果

通过强制事务排序，使其不可能相互冲突，从而解决幻读问题。简而言之，就是在每个读的数据行上加上共享锁实现，这个级别会导致大量的超时现象和锁竞争，一般不推荐使用

为了解决数据库并发控制问题，如走到同一时刻客户端对同一张表做更新或者查询操作，需要对并发操作进行控制，因此产生了锁

共享锁的粒度是行或者元组（多个行），一个事务获取了共享锁以后，可以对锁定范围内的数据执行读操作

排他锁的粒度与共享锁相同，一个事务获取排他锁以后，可以对锁定范围内的数据执行写操作

有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能获取这个元组的排他锁，必须等到事务 A 释放共享锁之后。如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁和意向排他锁。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。有意表示事务想执行操作但还没真正执行

锁的粒度主要分为表锁和行锁

表锁的开销最小，同时允许的并发量也是最小。MyISAM 存储引擎使用该锁机制。当要写入数据时，整个表记录被锁，此时其他读/写动作一律等待。一些特定的动作，如 ALTER TABLE 执行时使用的也是表锁

行锁可以支持最大的并发，InnoDB 存储引擎使用该锁机制。如果要支持并发读/写，建议采用 InnoDB 存储引擎

轻松掌握MySQL数据库锁机制的相关原理[1]

在一个update和insert操作频繁的表中 少量数据测试的时候运行良好 在实际运营中 因数据量比较大( 万条记录) 会出现死锁现象 用show processlist查看 可以看到一个update语句状态是Locked 一个delete语句状态是Sending data 查看了一下参考手册 把锁定相关的资料整理下来 以便自己记录和追踪该问题的解决情况

MySQL 支持对MyISAM和MEMORY表进行表级锁定 对BDB表进行页级锁定 对InnoDB 表进行行级锁定 在许多情况下 可以根据培训猜测应用程序使用哪类锁定类型最好 但一般很难说出某个给出的锁类型就比另一个好 一切取决于应用程序 应用程序的不同部分可能需要不同的锁类型 为了确定是否想要使用行级锁定的存储引擎 应看看应用程序做什么并且混合使用什么样的选择和更新语句 例如 大多数Web应用程序执行许多选择 而很少进行删除 只对关键字的值进行更新 并且只插入少量具体的表 基本MySQL MyISAM设置已经调节得很好

在MySQL中对于使用表级锁定的存储引擎 表锁定时不会死锁的 这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理

对WRITE MySQL使用的表锁定方法原理如下

◆ 如果在表上没有锁 在它上面放一个写锁

◆否则 把锁定请求放在写锁定队列中

对READ MySQL使用的锁定方法原理如下

◆如果在表上没有写锁定 把一个读锁定放在它上面

◆否则 把锁请求放在读锁定队列中

当一个锁定被释放时 锁定可被写锁定队列中的线程得到 然后是读锁定队列中的线程

这意味着 如果你在一个表上有许多更新 SELECT语句将等待直到没有更多的更新

如果INSERT 语句不冲突 可以自由为MyISAM 表混合并行的INSERT 和SELECT 语句而不需要锁定

InnoDB 使用行锁定 BDB 使用页锁定 对于这两种存储引擎 都可能存在死锁 这是因为 在SQL语句处理期间 InnoDB 自动获得行锁定 BDB 获得页锁定 而不是在事务启动时获得

行级锁定的优点

· 当在许多线程中访问不同的行时只存在少量锁定冲突

· 回滚时只有少量的更改

· 可以长时间锁定单一的行

行级锁定的缺点

· 比页级或表级锁定占用更多的内存

· 当在表的大部分中使用时 比页级或表级锁定速度慢 因为你必须获取更多的锁

· 如果你在大部分数据上经常进行 GROUP BY 操作或者必须经常扫描整个表 比其它锁定明显慢很多

· 用高级别锁定 通过支持不同的类型锁定 你也可以很容易地调节应用程序 因为其锁成本小于行级锁定

在以下情况下 表锁定优先于页级或行级锁定

· 表的大部分语句用于读取

· 对严格的关键字进行读取和更新 你可以更新或删除可以用单一的读取的关键字来提取的一行

# ; UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;

# ; DELETE FROM tbl_name WHERE unique_key_col = key_value ;

· SELECT 结合并行的INSERT 语句 并且只有很少的UPDATE或 DELETE 语句

· 在整个表上有许多扫描或 GROUP BY 操作 没有任何写操作

lishixinzhi/Article/program/MySQL/201311/29594

巧用MySQL InnoDB引擎锁机制解决死锁问题[2]

索引 KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

分析 涉及的两条语句应该不会涉及相同的TSK_TASK记录 那为什么会造成死锁呢？

查询MySQL官网文档 发现这跟MySQL的索引机制有关 MySQL的InnoDB引擎是行级锁 我原来的理解是直接对记录进行锁定 实际上并不是这样的

要点如下:

不是对记录进行锁定 而是对索引进行锁定

在UPDATE DELETE操作时 MySQL不仅锁定WHERE条件扫描过的所有索引记录 而且会锁定相邻的键值 即所谓的next key locking

如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID 会锁定所有主键大于等于 的所有记录 在该语句完成之前 你就不能对主键等于 的记录进行操作

当非簇索引(non cluster index)记录被锁定时 相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作

再分析一下发生问题的两条SQL语句 就不难找到问题所在了

当 update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where STATUS_ID= and MON_TIME

假设 update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where ID in ( ) 几乎同时执行时 本语句首先锁定簇索引(主键) 由于需要更新STATUS_ID的值 所以还需要锁定KEY_TSKTASK_MONTIME 的某些索引记录

这样第一条语句锁定了KEY_TSKTASK_MONTIME 的记录 等待主键索引 而第二条语句则锁定了主键索引记录 而等待KEY_TSKTASK_MONTIME 的记录 在此情况下 死锁就产生了

笔者通过拆分第一条语句解决死锁问题

先查出符合条件的ID select ID from TSK_TASK where STATUS_ID= and MON_TIME date_sub(now() INTERVAL minute) 然后再更新状态 update TSK_TASK set STATUS_ID= where ID in (… )

至此 死锁问题彻底解决

lishixinzhi/Article/program/MySQL/201311/29601