MySQL优化笔记其三

锁机制 MyISAM引擎

表锁

• 查看加锁情况

show open tables;

• 手动增加表锁

lock table 表名1 read(write),表名2 read(write),其他;

• 手动释放锁

unlock tables;

• read锁举例: session_1对dept表加read锁,seesion_1和session_2(泛指其他session)均可读dept，但是session_1无法对dept表写数据，也无法读取其他未锁定的表。session_2可以读取其他表，但是写数据进dept表被阻塞，等待获取锁。加read锁后，session_1无法写入数据到dept表以及读取其他表的数据。
• write锁举例: session_1对dept加write锁，session_1无法读取以及写其他未加锁的表.session_2读取和写dept表时被阻塞。但是session_2可以读取其他表。
• read锁是共享锁，write锁是独占锁。
• 查看数据库加锁争用概况

show status like 'table%';

-- Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
-- Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1),此值越高说明存在着较严重的表级锁争用情况。

• MyISAM引擎是写锁优先，因此，该引擎不适合做写为主表的引擎。因为加写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

InnoDB引擎 行锁

• 行锁支持事务

丢失更新(Lost Update): 多个事务操作同一行，每个事务不知道其他事务的存在，最后的事务更新覆盖了其他事务的更新。如果一个事务完成并提交之前，其他事务不能访问该行数据，则可避免此问题。
脏读(Dirty Reads): 事务B读到了事务A已修改但尚未提交的数据。此时如果事务A回滚，B读取到的数据无效，不符合一致性要求。设置读已提交(Read Committed)事务隔离级别可以避免此问题。
幻读(Phantom Reads): 一个事务按照相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足查询条件的新数据。不符合隔离性。
不可重复读(Non-Repeatable Reads): 一个事务在读取某些数据后的某个时间，再次读取以前读取过的数据，却发现其读出的数据已经发生了改变，或某些记录已经被删除了，这种现象称为"不可重复读"。
ACID:

• MySQL默认的事务隔离级别为"可重复读"。
• 事务的隔离级别

—	读数据的一致性	脏读	不可重复读	幻读
未提交读(Read uncommitted)	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读(Read committed)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

• 查看当前数据的事务隔离级别:

show variables like '%isolation%';

• session_1更新后未提交，session_2对同一行的更新会被阻塞。
• 索引失效导致行锁升级为表锁。例如:int和varchar自动隐式转换
• 间隙锁的危害

用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)。InnoDB也会对这个间隙加锁，这种锁机制就是间隙锁。例如:范围a>1 and a<6,其中a不存在2这个值。

语句执行过程中通过范围查找用到行锁的话，InnoDB会锁定整个范围内所有的索引键值，即使这个键值不存在。

• 锁定一行

select xxx[,xxx...] for update; 

• 行锁分析

show status like 'innodb_row_lock%';
-- innodb_row_lock_current_waits: 当前正在等待锁定的数量
-- innodb_row_lock_time: 系统启动到现在锁定总时间长度
-- innodb_row_lock_time_max: 系统启动到现在等待最长的一次所花费的时间
-- innodb_row_lock_waits: 系统启动到现在总共等待的次数