≡
  • 网络编程
  • 数据库
  • CMS技巧
  • 软件编程
  • PHP笔记
  • JavaScript
  • MySQL
位置:首页 > 数据库 > MySQL

MySQL InnoDB之事务与锁分析

人气:355 时间:2020-07-09

这篇文章主要为大家详细介绍了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;

本文来自:http://www.q1010.com/177/10258-0.html

注:关于MySQL InnoDB之事务与锁分析的内容就先介绍到这里,更多相关文章的可以留意四海网的其他信息。

关键词:MYSQL

您可能感兴趣的文章

  • MySQL的存储过程学习小结 附pdf文档下载
  • MySQL服务1067错误多种解决方案分享
  • 深入理解MySQL SET NAMES和MySQL(i)_set_charset的区别
  • MySQL启动与数据库的创建方法[图文]
  • 深入MySQL字符集设置分析
  • 揭秘SQL优化技巧 改善数据库性能
  • IP处理函数inet_aton()和inet_ntoa()使用说明
  • MySQL数据库互为主从配置方法分享
  • MySQL中校对集utf8_unicode_ci与utf8_general_ci的区别说明
  • MySQL #1062 –Duplicate entry '1' for key 'PRIMARY'
上一篇:MySQL 浅谈MyISAM 存储引擎
下一篇:MySQL中校对集utf8_unicode_ci与utf8_general_ci的区别说明
热门文章
  • mysql 修改character_set_server为utf-8的简单示例
  • 解决MySQL丢失文件localhost.localdomain.pid、mysql.sock的示例
  • MySQL 数据类型binary和varbinary的简单示例
  • MySQL:reading initial communication packet问题解决方法
  • MySql 表类型MYISAM、InnoDB区别
  • bash: mysql: command not found 的解决方法
  • MYSQL默认用户名ROOT修改方法
  • MySQL 常用命令菜鸟教程
  • MySQL 使用命令行新建用户并授予权限
  • MySql 数据库物理文件存放位置查看示例
  • 最新文章
    • MySQL查看死锁与解除死锁的简单示例
    • MySQL 慢查询的功能实例
    • MySQL查看死锁与去除死锁的简单示例
    • MySQL找出未提交事务的SQL的简单示例
    • MySQL锁阻塞的的简单示例
    • MySQL中的binary类型使用操作的示例
    • SQL优化教程之in与range查询的简单示例
    • MySQL 的 21 个规范、优化最佳实践!
    • MySQL 字符类型大小写敏感的简单示例
    • 解决mybatis-plus分页传入参数后sql where条件没有limit分页信息的问题

四海网收集整理一些常用的php代码,JS代码,数据库mysql等技术文章。