这个例子里,事务一的更新是无效的,尽管在这个事务里程序认为还存在(10,10)记录。
事务一中更新之前的SELECT操作是快照读,所以读到了快照里的(10,10),而UPDATE中的WHERE子句是当前读,取得是最新版本的数据,所以matched: 0 Changed: 0。
如果上述例子中的操作是对同一条记录做修改,就会引起更新丢失。例如,事务一和二同时开启,事务一先执行update test set code=100 where id=10;,事务二再执行update test set code=200 where id=10;,事务一的更新就会被覆盖。
这就是经典的丢失更新问题,英文叫Lost Update,又叫提交覆盖,因为是最后执行更新的事务提交导致的覆盖。还有一种更新丢失叫做回滚覆盖,即一个事务的回滚把另一个事务提交的数据给回滚覆盖了,但是目前市面上所有的数据库都不支持这种stupid的操作,因此不再详述。
4.5.2 乐观锁与悲观锁这种情况下,引入我们常见的两种方式来解决该问题
乐观锁:在UPDATE的WHERE子句中加入版本号信息来确定修改是否生效
悲观锁:在UPDATE执行前,SELECT后面加上FOR UPDATE来给记录加锁,保证记录在UPDATE前不被修改。SELECT ... FOR UPDATE是加上了X锁,也可以通过SELECT ... LOCK IN SHARE MODE加上S锁,来防止其他事务对该行的修改。
无论是乐观锁还是悲观锁,使用的思想都是一致的,那就是当前读。乐观锁利用当前读判断是否是最新版本,悲观锁利用当前读锁定行。
但是使用乐观锁时仍然需要非常谨慎,因为RR是可重复读的,一定不能在UPDATE之前先把版本号读取出来。
如果一个SQL语句要对二级索引(非主键索引)设置X模式的Record锁,InnoDB还会检索出相应的聚簇索引(主键索引)并对它们设置锁定。
5.1 SELECT ... FROM...不加锁SELECT ... FROM是快照读取,除了SERIALIZABLE的事务隔离级别,该SQL语句执行时不会加任何锁。
SERIALIZABLE级别下,SELECT语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引,只锁定索引记录,而不会锁定gap。
5.2 UPDATE系列S锁读取(SELECT ... LOCK IN SHARE MODE),X锁读取(SELECT ... FOR UPDATE)、更新UPDATE和删除DELETE这四类语句,采用的锁取决于搜索条件中使用的索引类型。
如果使用唯一索引,InnoDB仅锁定索引记录本身,不锁定间隙。
如果使用非唯一索引,或者未命中索引,InnoDB使用间隙锁或者next-key锁来锁定索引范围,这样就可以阻止其他事务插入锁定范围。
5.2.1 UPDATE语句UPDATE ... WHERE ...在搜索遇到的每条记录上设置一个独占的next-key锁,如果是唯一索引只锁定记录。
当UPDATE修改聚簇索引时,将对受影响的二级索引采用隐式锁,隐式锁是在索引中对二级索引的记录逻辑加锁,实际上不产生锁对象,不占用内存空间。
例如update test set code=100 where id=10;执行的时候code=10的索引(code是二级索引,见文中给出的建表语句)会被加隐式锁,只有隐式锁产生冲突时才会变成显式锁(如S锁、X锁)。即此时另一个事务也去更新id=10这条记录,隐式锁就会升级为显示锁。
这样做的好处是降低了锁的开销。
UPDATE可能会导致新的普通索引的插入。当新的索引插入之前,会首先执行一次重复索引检查。在重复检查和插入时,更新操作会对受影响的二级索引记录采用共享锁定(S锁)。
5.2.2 DELETE语句DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置一个独占的next-key锁,如果是唯一索引只锁定记录。
5.3 INSERTINSERT区别于UPDATE系列单独列出,是因为它的处理方式较为特别。