每天进步一点点————事务控制（3）

恢复和复制的需要，对InnoDB锁机制的影响

MySQL通过BINLOG记录执行承德INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制.

 

MySQL5.6支持3种日志格式：

                   基于语句的日志格式SBL

                   基于行的日志格式RBL

                   和混合格式

 

它还支持4中复制模式：

                   基于SQL语句的复制SBR:这也是MySQL最早支持的复制模式

                   基于行的数据复制RBR：这是MySQL5.1以后开始支持的复制模式，优点是支持对非安全SQL复制。

                   混合复制模式：对安全的SQL语句采用基于SQL语句的复制模式，对于非安全的SQL语句采用居于行的复制模式。

                   使用全局事务ID（GTIDs）的复制：主要是解决主从自动同步一致问题。

 

对于SBL的恢复而言，必须满足：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许有幻读。

对于“insert into target select * from source_tab where ....”和“createtable new_tab

 ....select .... from source_tab where ....”这种语句用户并没有对source_tab做任何操作，但是MySQL对此表却加了共享锁，并没有使用多版本数据一致性读取技术。这也保证了恢复和复制的正确性。

如何关闭此锁：将innodb_locks_unsafe_for_binlog设置成"on"则InnoDB不给soruce_tab加锁。但是这样生成的二进制日志文件恢复数据库会与应用逻辑不符，而如果进行复制则会导致主从数据库不一致。

如果应用中一定要使用这种SQL来实现业务逻辑，又不希望对源表并发更新产生影响可采取以下三种措施

         一是将innodb_locaks_unsafe_for_binlog的值设置为“on”强制MySQL使用多版本数据一致性度，但是付出代价是可能无法用BINLOG正确地恢复或者复制数据。因此不推荐使用这种方式。

         二是通过使用“select* from source_tab ... into outfile ”和“load datainfile...”语句组合实现，采用这种方式MySQL不会给source_tab加锁。

         三是使用基于行的BINLOG格式和基于行数据的复制。

1.   InnoDB在不同隔离级别下的一致性度及锁的差异

		read uncommited	read commited	repeatable read	serializable
select	相等	none locks	none locks	consisten read/none locks	share locks
	范围	none locks	none locks	consisten read/none locks	share next-key
update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks next key	exclusive locks next key	exclusive locks next key	exclusive locks next key
replace	无冲突键	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	有冲突键	exclusive locks next key	exclusive locks next key	exclusive locks next key	exclusive locks next key
delete	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks next key	exclusive locks next key	exclusive locks next key	exclusive locks next key
insert	N/A	exclusive locks	exclusive locks	exclusive locks	exclusive locks
select ...from ..lock share mode	相等	share locks	share locks	share locks	share locks
	范围	share locks	share locks	share next-key	share next-key
select * from ...for update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks next key	exclusive locks next key	exclusive locks next key	exclusive locks next key
insert into ... select ... (原表)	innodb_locks_unsafe_for_binlog=off	share next-key	share next-key	share next-key	share next-key
	innodb_locks_unsafe_for_binlog=on	none locks	consisten read/none locks	consisten read/none locks	share next-key
create table ... select ... (原表)	innodb_locks_unsafe_for_binlog=off	share next-key	share next-key	share next-key	share next-key
	innodb_locks_unsafe_for_binlog=on	none locks	consisten read/none locks	consisten read/none locks	share next-key

 

2.   什么时候使用表锁

         对于Inn第一种oDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择InooDB表的理由。

         第一种：事务需要大量跟新或者全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突。这种情况下可以考虑使用表锁来提高该事务的执行速度。

         第二种：事务涉及多高表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

当然这两种事务不能太多，否则应该考虑使用MyISAM表了。

InnoDB使用表锁要注意两点：

         1。使用locktables 虽然可以给InnoDB加表级锁，但是必须说明的是表锁不是有InnoDB存储引擎管理而是由MySQL Server负责，仅当autocommit=0、innodb_table_locks=1时InnoDB才知道MySQL加的表锁，MySQL也才能感知InnoDB是否加了行锁。否则会出现死锁

         2。在用LOCKTABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前不要用UNLOCK TABLE释放表锁。因为UNLOCK TABLE会银行地提交事务；COMMIT或者ROLLBACK并不能释放用LOCK TABLES加的表及锁，必须使用UNLCOK TABLES 释放表锁。

3.   死锁

在InnoDB中，粗了单个SQL组成事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁的可能。

 

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁，并退回，另一事务获得锁，继续完成事务。

 

但是在涉及外部锁或者涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。这个参数是用来设置锁等待超时时间的。

几种避免死锁的方法

1.        在应用中，如果不通的程序会方式存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

2.        在程序以批量方式处理数据时候，如果实现对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

3.        在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不能先申请共享锁，然后等共享的时候再申请排他锁。

4.        在REPEATABLE-READ隔离级别下，如果两个线程对相同的条件记录用select ... for update加排他锁，在没有符合该条件记录的情况下，两个线程都会加锁成功。如果两个线程都这么做，就会出现死锁。这种情况下将隔离级别改成read committed，就可以避免了。

5.        当隔离界别为READ COMMITTED时，如果两个线程都先执行select ... for update,判断是否存在符合的记录，如果没有就插入记录。这种情况如果因为有主键，不可以插入重复行，第一个线程插入成功后第二个线程会因为主键重复出错，但是此时第二个线程已经获得了排他锁，如果有第三个线程又来申请排他锁，也会出现死锁情况。对于这种情况，可以直接做插入操作，然后再捕获主键重复异常，或遇到主键重错误时，总是执行ROLLBACK释放获得排他锁。

尽管通过上面介绍的涉及和SQL优化等措施，可以大大减小死锁，但是死锁很难完全避免。可以通过SHOW ENGINE INNODB STATUS 命令来确定最后一个死锁产生的原因。

mysql>show engine innodb status\G;

 

....

2015-08-25 16:24:29 7fb07b24a700

*** (1) TRANSACTION:

TRANSACTION 146929, ACTIVE 137 sec startingindex read

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1248,202 row lock(s)

MySQL thread id 8, OS thread handle0x7fb07b28b700, query id 2818824 localhost root updating

update actor set last_name='MONROE T' whereactor_id=178

*** (1) WAITING FOR THIS LOCK TO BEGRANTED:

RECORD LOCKS space id 236 page no 3 n bits272 index `PRIMARY` of table `sakila`.`actor` trx id 146929 lock_mode X locksrec but not gap waiting

Record lock, heap no 179 PHYSICAL RECORD:n_fields 6; compact format; info bits 0

 0:len 2; hex 00b2; asc   ;;

 1:len 6; hex 0000000229ac; asc     ) ;;

 2:len 7; hex c60000020808dc; asc        ;;

 3:len 4; hex 4c495341; asc LISA;;

 4:len 6; hex 4d4f4e524f45; asc MONROE;;

 5:len 4; hex 43f23ed9; asc C > ;;

 

*** (2) TRANSACTION:

TRANSACTION 146928, ACTIVE 170 sec startingindex read

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1248, 202 rowlock(s)

MySQL thread id 7, OS thread handle 0x7fb07b24a700,query id 2818825 localhost root updating

update actor set last_name='MONROE T' whereactor_id=178

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 236 page no 3 n bits272 index `PRIMARY` of table `sakila`.`actor` trx id 146928 lock mode S

Record lock, heap no 1 PHYSICAL RECORD:n_fields 1; compact format; info bits 0

 0:len 8; hex 73757072656d756d; asc supremum;;

.......