MySQL InnoDB之事务与锁详解

引题：为何引入事务？

1>.数据完整性

2>.数据安全性

3>.充分利用系统资源，提高系统并发处理的能力

1. 事务的特征

事务具有四个特性：原子性（Atomiocity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这四个特性简称ACID特性。

1.1原子性

事务是数据库的逻辑工作单位，事务中包括的所有操作要么都做，要么都不做。

1.2 一致性

事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态。

1.3 隔离性

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他

事务是隔离的，并发执行的各个事务之间互相不干扰。

1.4 持久性

一个事务一旦成功提交，对数据库中数据的修改就是持久性的。接下来其他的其他

操作或故障不应该对其执行结果有任何影响。

2. MySQL的InnoDB引擎中事物与锁

2.1 SELECT …… LOCK IN SHARE MODE

会话事务中查找的数据，加上一个共享锁。若会话事务中查找的数据已经被其他会话事务加上独占锁的话，共享锁会等待其结束再加，若等待时间过长就会显示事务需要的锁等待超时。

2.2 SELECT ….. FOR UPDATE

会话事务中查找的数据，加上一个读更新琐，其他会话事务将无法再加其他锁，必须等待其结束。

2.3 INSERT、UPDATE、DELETE

会话事务会对DML语句操作的数据加上一个独占锁，其他会话的事务都将会等待其释放独占锁。

2.4 gap and next key lock（间隙锁）

InnoDB引擎会自动给会话事务中的共享锁、更新琐以及独占锁，需要加到一个区间值域的时候，再加上个间隙锁（或称范围锁），对不存在的数据也锁住，防止出现幻写。

备注：

以上2.1，2.2，2.3，2.4中描述的情况，跟MySQL所设置的事务隔离级别也有关系。

3.四种事务隔离模式

3.1 READ UNCOMMITED

SELECT的时候允许脏读，即SELECT会读取其他事务修改而还没有提交的数据。

3.2 READ COMMITED

SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致。

3.3 REPEATABLE READ

SELECT的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的。

3.4 SERIALIZABLE

与可重复读的唯一区别是，默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。

4. 验证事务与锁定示例

接下来，我们将以MySQL中的InnoDB引擎，解释其如何实现ACID特性，不同隔离级别下事务与事务之间的影响。示例表结构：

CREATE TABLE `account ` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`VACCOUNT_ID` varchar(32) NOT NULL,

`GMT_CREATE` datetime NOT NULL,

PRIMARY KEY (`ID`),

KEY `idx_VACCOUNT_PARAMETER_VACCOUNTID ` (`VACCOUNT_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

然后向表account中写入10W条创建日期分布合理的帐号数据，以方便测试之用。
































































tx_isolation：SET GLOBAL tx_isolation='read-uncommitted'
ID 事务1 事务1输出 事务2 事务2输出
1 START TRANSACTION;      
2 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
      START TRANSACTION;  
3     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;  
4     SELECT VACCOUNT_ID from account  where ID =1001; uncommitted
5 SELECT VACCOUNT_ID from account  where ID =1001; uncommitted    
6     ROLLBACK;  
7 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
8 COMMIT;      
































































tx_isolation：SET GLOBAL tx_isolation='read-committed'
ID 事务1 事务1输出 事务2 事务2输出
1 START TRANSACTION;      
2 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
3     START TRANSACTION;  
4     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;  
5     SELECT VACCOUNT_ID from account  where ID =1001; uncommitted
6 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
7     COMMIT;  
8 SELECT VACCOUNT_ID from account  where ID =1001; uncommitted    
9 COMMIT;      
































































tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ'
ID 事务1 事务1输出 事务2 事务2输出
1 START TRANSACTION;      
2 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
3     START TRANSACTION;  
4     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;  
5     SELECT VACCOUNT_ID from account  where ID =1001; uncommitted
6 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
7     COMMIT;  
8 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
9 COMMIT;      
























































































tx_isolation：SET GLOBAL tx_isolation='SERIALIZABLE'
ID 事务1 事务1输出 事务2 事务2输出
1 START TRANSACTION;      
2 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
3     START TRANSACTION;  
4     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; STATE: Updating
5 SELECT VACCOUNT_ID from account  where ID =1001; caimao101510    
      事务2超时  
6 COMMIT;      
7 START TRANSACTION;      
8 UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;      
9     START TRANSACTION;  
10     SELECT VACCOUNT_ID from account  where ID =1001; STATE：statistics
11     事务2超时  
12 commit;      

















































































































































































tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ'
ID 事务1 事务1输出 事务2 事务2输出
1 START TRANSACTION;      
2 select max(ID) FROM account; 124999    
3     START TRANSACTION;  
4 UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID >=124999;      
5     insert into account(VACCOUNT_ID,gmt_create) values(‘eugene',now()); STATE:update
6     事务2超时  
7     START TRANSACTION;  
8     SELECT * FROM account WHERE ID =124998; 2007-10-20 13:47
9     UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID =124998; 执行成功
10     SELECT * FROM account WHERE ID =124998; 2007-10-21 13:47
11 COMMIT;      
12     COMMIT;  
         
1 START TRANSACTION;      
2 UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create >'2009-07-01′;      
3     START TRANSACTION;  
4     SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; 2009-10-2 13:47
5 SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; 2009-10-1 13:47   STATE:update
6     insert into account(VACCOUNT_ID,gmt_create) values(‘gmt_create_test',now());  
7     事务2超时  
8 COMMIT;      
9     SELECT * FROM account WHERE gmt_create>'2009-07-10′ LIMIT 1; 2009-10-1 13:47
无索引条件更新事务
1 START TRANSACTION;      
  UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create >'2009-07-01′ AND gmt_create <'2009-07-10′;      
      START TRANSACTION;  
      insert into account(VACCOUNT_ID,gmt_create) values(‘gmt_create_interval',now());  
      事务2超时  
  COMMIT;

赞 (0)

相关推荐

  • MySQL中InnoDB存储引擎的锁的基本使用教程

    MySQL中InnoDB存储引擎的锁的基本使用教程

    MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 各种锁特点 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生冲突的概率最高,并发度最低 行级锁:开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也最高 页面锁:开销和加锁时间介于表锁和行锁之间:会出现死锁:锁定粒度介于表锁和行锁之

  • 详谈innodb的锁(record,gap,Next-Key lock)

    详谈innodb的锁(record,gap,Next-Key lock)

    Record lock单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引.所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的. Gap lock在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身.gap lock的机制主要是解决可重复读模式下的幻读问题,关

  • mysql之innodb的锁分类介绍

    一.innodb行锁分类 record lock:记录锁,也就是仅仅锁着单独的一行 gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值. next-key lock:record lock+gap lock,所以next-key lock也就半开半闭区间,且是下界开,上界闭. www.jb51.net next-key 锁定范围:(负无穷大,最小第一记录],(记录之间],(最大记录,正无穷大) 二.语句锁定情况分析 SELECT ... FROM ... F

  • MySQL InnoDB之事务与锁详解

    引题:为何引入事务? 1>.数据完整性 2>.数据安全性 3>.充分利用系统资源,提高系统并发处理的能力 1. 事务的特征 事务具有四个特性:原子性(Atomiocity).一致性(Consistency).隔离性(Isolation)和持久性(Durability),这四个特性简称ACID特性. 1.1原子性 事务是数据库的逻辑工作单位,事务中包括的所有操作要么都做,要么都不做. 1.2 一致性 事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态. 1.3 隔离性 一

  • MySQL 四种事务隔离级别详解及对比

    MySQL 四种事务隔离级别详解及对比 按照SQL:1992 事务隔离级别,InnoDB默认是可重复读的(REPEATABLE READ).MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别.你可以在命令行用--transaction-isolation选项,或在选项文件里,为所有连接设置默认隔离级别. 例如,你可以在my.inf文件的[mysqld]节里类似如下设置该选项: transaction-isolation = {READ-UNCOMMITTED | READ-CO

  • Mysql InnoDB多版本并发控制MVCC详解

    Mysql InnoDB多版本并发控制MVCC详解

    目录 一丶为什么需要事务隔离级别 1.实现事务隔离的方式:串行执行 2.实现事务隔离的方式:可串行执行 二丶并发事务执行的问题:脏写,脏读,不可重复读,幻读 1.脏写 2.脏读 3.不可重复读 4.幻读 三丶隔离级别 1.Read UnCommitted 读未提交 2.Read Committed 读已提交 3.Repeatable Read 可重复读 4.Serializable 可串行化 四丶Mysql设置隔离级别 1.设置全局隔离级别 2.设置会话隔离级别 3.设置下一个事务的隔离级别 4

  • MySQL InnoDB引擎的缓存特性详解

    MySQL InnoDB引擎的缓存特性详解

    目录 1. 背景 2. 存储器性能差异 3. Buffer Pool 4. Free链表 5. Flush链表 6. LRU链表 7. 其它 1. 背景 对于各种用户数据.索引数据等各种数据都是需要持久化存储到磁盘,然后以“页”为单位进行读写. 相对于直接读写缓存,磁盘IO的成本相当高昂. 对于读取的页面数据,并不是使用完就释放掉,而是放到缓冲区,因为下一次操作有可能还需要读区该页面. 对于修改过的页面数据,也不是马上同步到磁盘,也是放到缓冲区,因为下一次有可能还会修改该页面的数据. 但是缓存的

  • MySQL四种事务隔离级别详解

    MySQL四种事务隔离级别详解

    本文实验的测试环境:Windows 10+cmd+MySQL5.6.36+InnoDB 一.事务的基本要素(ACID) 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节.事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样.也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,

  • Mysql InnoDB的锁定机制实例详解

    1.InnoDB的锁定机制 InnoDB存储引擎支持行级锁,支持事务处理,事务是有一组SQL语句组成的逻辑处理单元,他的ACID特性如下: 原子性(Atomicity): 事务具有原子不可分割的特性,要么一起执行,要么都不执行. 一致性(Consistency): 在事务开始和事务结束时,数据都保持一致状态. 隔离性(Isolation): 在事务开始和结束过程中,事务保持着一定的隔离特性,保证事务不受外部并发数据操作的影响. 持久性(Durability): 在事务完成后,数据将会被持久化到数

  • MySQL数据库的事务和索引详解

    MySQL数据库的事务和索引详解

    目录 一.事务: 事务四大特性: 并发事务带来哪些问题?(隔离所导致的一些问题) 事务隔离级别有哪些? MySQL的默认隔离级别: 二.索引: 索引的作用: 索引的分类: 索引准则: 索引的数据结构: 总结 一.事务: 事务是逻辑上的一组操作,要么都成功,要么都失败! ---------------------------------- 1.SQL执行        A:1000元     -->转账200元        B:200元 2.SQL执行        A:800元       -

  • MySQL InnoDB表空间加密示例详解

    前言 从 MySQL5.7.11开始,MySQL对InnoDB支持存储在单独表空间中的表的数据加密 .此功能为物理表空间数据文件提供静态加密.该加密是在引擎内部数据页级别的加密手段,在数据页写入文件系统时加密,加密用的是AES算法,而其解密是在从文件读到内存中时进行. 1 配置加密插件 1.1 修改配置文件 在mysql配置文件[mysqld]x项中添加如下内容 plugin_dir=/usr/local/mysql5.7/lib/mysql/plugin # 插件路径,根据实际情况修改 ear

  • mysql的XA事务恢复过程详解

    mysql数据库开机报错: InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 100224 12:24:20 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace

  • MySQL事务隔离机制详解

    MySQL事务隔离机制详解

    目录 前言 一.什么是数据库事务 二.事务并发带来的4类问题 三.事务4种隔离级别 四.Mysql演示4种隔离级别 总结 参考文章: 前言 如何控制并发是数据库领域中非常重要的问题之一,MySQL为了解决并发带来的问题,设计了事务隔离机制.锁机制.MVCC机制,用一整套机制来解决并发问题,本文主要介绍事务隔离机制. 一.什么是数据库事务 事务transaction(简写tx),在数据库中,事务是指一组逻辑操作,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位.事务由事务开始与结束之

随机推荐