MySQL InnoDB存储引擎排它锁和共享锁的研究

1，共享锁实验

session1

在session1建表lisa并插入数据

mysql> create table lisa(name char(10),age int(5));

mysql> insert into lisa values('lisa','26');

加给age=26这一行加共享锁

mysql> set autocommit=0;

mysql> select * from lisa where age=26 lock in share mode;

mysql> update lisa set age='99' where name = 'lisa';

更新成功！如下图：

session2

无法更新和查询，如下所示：

总结： 在InnoDB存储引擎中共享锁和排它锁是行锁，必须使用where指定行再加锁。

加共享锁后，别的session无法更新和查询。

2，排他锁实验

session2

在session1建表sun并插入数据

mysql> create table sun(name char(10),age int(4));

mysql> insert into sun values('lisa','26');

加给age=26这一行加排他锁

mysql> set autocommit = 0;

mysql> select * from sun where age='26' for update;

mysql> update sun set name='winter' where age='26';

更新成功！如下图：

session3

在session3无法再给age=26这行加排它锁，也无法更新age=26这行，并且查询到的数据也不是session2中最新更新的数据。

-----------------------------------------------------------------

在session2中建立新表tab_no_index并插入数据

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;

mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

mysql> set autocommit=0;

mysql> select * from tab_no_index where id=1;

mysql> select * from tab_no_index where id=1 for update;

给id=1这一行加排他锁如下图

在session1中查询新表ab_no_index报错啦！

mysql> select * from tab_no_index;
ERROR 1412 (HY000): Table definition has changed, please retry transaction

我所做的操作就是在该session1给表lisa加了行的共享锁，

为何我查询别的表会报错呢？？？

如何解决错误？？

解决办法：

mysql> commit;

错误原因：

我创建表的事务隔离级别是可重复读（repeatable-read），

不是读提交(read-committed),在别的session查询时需要commit或者rollback一下才能查到我更新的数据。就是第N次select跟第一次select的值都是一样的,只有commit或者rollback等操作才能看到改变的值.

下图是在创建表的session也就是本文的session2中做的操作

转载于:https://blog.51cto.com/songlisha/1885119