MySQL InnoDB存储引擎锁机制实验

之前的文章提到MySQL的InnoDB存储引擎使用的是行级锁，并且默认的事务隔离级别为可重复读，而不同于Oracle默认的事务隔离级别提交读。那么MySQL的InnoDB存储引擎的锁机制的具体表现是怎样的呢？相关阅读：MySQL数据库锁机制 http://www.linuxidc.com/Linux/2013-03/81532.htm实验如下：

首先建立一张测试使用的表：

CREATE TABLE`test_innodb_lock` （

`a` int（11） DEFAULT NULL,

`b` varchar（16） DEFAULT NULL,

KEY `test_innodb_lock_a_IDX` （`a`）

） ENGINE=InnoDB

然后再往这张表里插入一些数据，以备使用，最终表数据如下：

+------+------+

| a | b |

+------+------+

| 1 | a |

| 1 | x |

| 1 | y |

| 2 | b |

| 2 | w |

| 2 | z |

| 3 | c |

| 4 | d |

| 5 | e |

| 8 | ff |

| 8 | f |

| 10 | g |

+------+------+

首先我们来看看行级锁的情况：

实验一：

打开两个MySQL客户端，

在客户端1执行:

mysql> set autocommit = 0;

Query OK, 0 rows affected （0.00 sec）

修改客户端1的事务提交方式为手动提交；

在客户端2执行：

mysql> set autocommit = 0;

Query OK, 0 rows affected （0.00 sec）

同样修改客户端2的事务提交方式为手动提交；

在客户端1执行：

mysql> update test_innodb_lock set b ="xxx" where a = 1 and b = "y";

Query OK, 1 row affected （0.00 sec）

Rows matched: 1 Changed: 1 Warnings: 0

同时使用索引字段a和非索引字段b更新一条数据；

在客户端2执行：

mysql> update test_innodb_lock set b ="xxx" where a=1 and b = "x";

同时使用索引字段a（并且索引值同客户端1的值相同）和非索引字段更新另外一条数据；

结果发现客户端2的update语句被阻塞，需要客户端1提交或回滚才能继续执行。说明，虽然两个事务最终更新的数据不是同一条数据，但然后可能被锁定，这是因为两条SQL语句都使用了相同的索引值（a=1），行级锁上升为页级锁。

实验二：

在客户端1执行：

mysql> rollback;

Query OK, 0 rows affected （0.00 sec）

回滚实验一的操作；

在客户端2执行：

mysql> rollback;

Query OK, 0 rows affected （0.00 sec）

回滚实验一的操作；

在客户端1执行：

mysql> update test_innodb_lock set b ="xxx" where a = 1 and b = "a";

Query OK, 1 row affected （0.00 sec）

Rows matched: 1 Changed: 1 Warnings: 0

同时使用索引字段a和非索引字段b更新一条数据；

在客户端2执行：

mysql> update test_innodb_lock set b ="xxx" where a=2 and b = "b";

Query OK, 1 row affected （0.00 sec）

Rows matched: 1 Changed: 1 Warnings: 0

同时使用索引字段a（索引值不同于客户端1SQL语句的索引值）和非索引字段b更新一条数据；

更新顺利进行，执行并没有被阻塞；

说明，同是根据索引和非索引字段进行更新数据，当两个事务的SQL语句中的索引条件值不一样时，更新仍然能够顺利进行。

Oracle 包内的存储过程的动态sqlOracle中的左右连接相关资讯 MySQL InnoDB MySQL锁机制 InnoDB存储引擎

MySQL Internal - InnoDB存储引擎（（03月03日）
MySQL innoDB 存储引擎学习篇（02/15/2015 10:26:16）
深入浅出MySQL事务处理和锁机制（01/11/2015 17:21:34）

MySQL锁机制详解及死锁处理方式（05/01/2015 21:08:38）
MySQL innoDB重做日志文件（01/29/2015 12:12:50）
MySQL InnoDB 共享表空间和独立表（01/04/2015 11:26:59）

本文评论查看全部评论（0）