mysql学习笔记之优化篇(二)之锁

对mysql的锁这块真的不是特别了解，学习完这一部分，有种豁然开朗的感觉。当然还是有待进一步提高。更加深入的学习。

锁问题　

mysql锁的分类

 1 表锁，开销小，加锁快，不会出现死锁，锁定粒度大
 2 行锁，开销大，加锁慢，会出现死锁，锁粒度小
 3 页面锁，介于两者之间，会出现死锁

myisam 表锁，适合读多的程序
innodb 行锁，适合写多和并发高的程序

查看表锁的状况

show status like 'table%';

| Table_locks_immediate      | 504   |
| Table_locks_waited         | 0  锁等待状态   |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0  

表锁又分为：表共享读锁，表独占写锁。

加表锁语法：lock table xxx read；lock table xxx write；

解除表锁：unlock tables;

并发插入

myisam的表读写都是串行的。其实也支持并发插入
通过concurent_insert 0 不允许  1 有空洞允许在表尾插入 2 允许表尾插入

myisam锁调度

读锁写锁是互斥的，读写操作是串行的。myisam的锁是写先行。
当然也可以调整myisam的锁的行为

innodb和myisam的最大的不同

1 innodb支持事务
2 采用了行锁

事务和acid属性

原子性。要么全部执行，要么全部不执行。
一致性。事务开始和完成时数据保持一致性。
隔离性。事务不受外部并发操作影响。
持久性。当事务完成后，数据的修改是永久性的

并发事务带来的问题

更新丢失。多事务操作同一条记录。可以采用锁定。
脏读。
不可重复读。
幻读。

事务隔离级别

实现方式
     1 加锁
     2 快照实现多版本控制

未提交读
已提交读
可重复读
可序列化

查看innodb事务状态

show status like '%innodb%';

innodb的行锁模式以及加锁方式

行锁
    共享锁S，允许一个事务去读一行，阻止其他事务获取相同数据集的排它S锁
    排他锁X，允许获得排它锁的事务更新数据，阻止其他事务获取相同的共享锁和排它锁
表锁
    意向共享锁IS
    意向排它锁IX

innodb锁的特别注意的

1 在不通过索引条件查询的时候，innodb确实用的是表锁，不是行锁。
2 mysql的行锁是针对索引加的锁，而不是针对记录，使用相同的索引键可能出现冲突。
3 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引，唯一索引，普通索引，innodb都是采用行锁来对数据加锁
4 即使创建了索引，能偶否使用索引还是由mysql判断，如果代价高，innodb可能采用表锁。

间隙锁-范围加锁

恢复机制对innodb锁的影响

myslql是通过binlog记录执行增删改查操作的。
    1 mysql恢复的是sql语句的集合
    2 通过binglog先后顺序恢复

innodb什么时候使用表锁

1 事务需要更新大部分或者全部数据，表又打的时候
2 实际多表，业务复杂，容易造成死锁，造成大量事务回滚的情况。

在使用mysql中如何尽量减少死锁的概率

尽量使用较低级别的隔离级别
精心设计索引，并尽量使用索引反问数据，使加锁更加精确
合理选择事务大小
申请合适的事务级别
如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表。
批量处理数据时，先对数据排序，保证每个线程以固定的顺序来访问记录
更新记录直接使用排它锁
更新同一条记录可以采用READ COMMITED级别