MySQL InnoDB之事务与锁详解

更新时间：2012年04月01日 16:42:24 作者：

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;

您可能感兴趣的文章:

MySQL中SHOW TABLE STATUS的使用及说明
这篇文章主要介绍了MySQL中SHOW TABLE STATUS的使用及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教
2023-10-10
在大数据情况下MySQL的一种简单分页优化方法
这篇文章主要介绍了在大数据情况下MySQL的一种简单分页优化方法,分页优化是MySQL优化的常用手段之一,需要的朋友可以参考下
2015-05-05
log引起的mysql不能启动的解决方法
今天服务器挂了原来服务器的硬盘进行了数据转移弄到mysql的时候发现里面log日志文件高达900MB
2008-07-07
mysql创建Bitmap_Join_Indexes中的约束与索引
现象：创建Bitmap Join Indexes时出现ORA-25954报错: 维的主键或唯一约束条件缺失。 53vi.Com 原因：受到约束与索引的影响。
2008-04-04
浅析MYSQL REPEATABLE-READ隔离级别
REPEATABLE-READ 即可重复读，autocommit= 0或者START TRANSACTION状态下select表的内容不会改变
2014-07-07
解读mysql datetime类型精确到毫秒、微秒的问题
这篇文章主要介绍了解读mysql datetime类型精确到毫秒、微秒的问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教
2023-02-02
Mysql中where与on的区别及何时使用详析
MySQL当中的限制条件可以使用on或者where,两者在不同的情况下具有不同而意义,这篇文章主要给大家介绍了关于Mysql中where与on的区别及何时使用的相关资料,需要的朋友可以参考下
2021-08-08
MySQL 集群迁移到 Kubernetes操作步骤
这篇文章主要为大家介绍了MySQL 集群迁移到 Kubernetes使用示例详解，有需要的朋友可以借鉴参考下，希望能够有所帮助，祝大家多多进步，早日升职加薪
2022-09-09
mysql外连接与内连接查询的不同之处
在关系型数据库中，我们经常是把数据存储到多个相互关联的表中，这些相互关联的表通过指定的列发生联系，下面这篇文章主要给大家介绍了关于mysql外连接与内连接查询的不同之处,需要的朋友可以参考下
2021-06-06
关于MySQL的sql_mode合理设置详解
这篇文章主要给大家介绍了关于MySQL的sql_mode合理设置的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用MySQL具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
2019-07-07