mysql 锁机制

锁是为了保护某一个资源同时被多个进程或线程修改，从而造成数据混乱。
锁粒度：
　　表级锁：对整张表加锁，开销小，加锁快，不会出现死锁，锁粒度大，锁竞争概率高，并发效率低
　　行级锁：对某行主键索引，开销大，加锁慢，会出现死锁，锁粒度小，锁竞争概率低，并发效率高
　　运用场景不同，选择合理锁，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据
　　MYISAM和MEMORY存储引擎采用的是表级锁，InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁
MYISAM表级锁：
　　当一个线程获取到表的读锁时，会阻塞其他用户对该表的写操作，但是不会阻塞其他用户对该表读操作，阻塞写操作，当一个线程获取到表的写锁时，会阻塞其他用户的读写操作，正常情况是自动加锁。

?

1 2 3 4 5 6 7 8

测试数据： CREATE TABLE `mylock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `mylock` VALUES (1, 'zhangsan'),(2, 'lisi'); commit;

　　表独占写锁：

?

1 2 3 4 5 6 7

--打开两个mysql窗口A,B --A窗口给mylock加写锁 A:lock table mylock write; A:select * from mylock; A:insert into mylock values (3,'wangwu'); B:select * from mylock; -- 出现阻塞 A:unlock tables; --释放锁

　　表共享读锁：

?

1 2 3 4 5 6 7 8 9

--A窗口给mylock加读锁 A:lock table mylock read; A:select * from mylock; A:select * from test_trancation; --报错，查询其他未加锁表 B:select * from mylock; B:insert into mylock values (4,'cuihua');--阻塞 A:insert into mylock values (5,'xiaozhu');--报错，当前为读锁 A:unlock tables; B:执行成功

　　查询表级锁争用情况：

?

1	show status like 'table_locks%';

InnoDB行级锁和表级锁：
　　通过给主键加锁，如果是通过其他索引访问，其他索引存储的也是主键值，未使用索引访问，则是会使用表级锁，即使使用索引也有可能加表级锁。mysql优化器认为表级锁效率更高时。
　　验证表级锁：

?

1 2 3 4 5 6 7

测试数据 CREATE TABLE `tab_no_index`( `id` int, `name` varchar(10) ) engine=innodb; insert into tab_no_index values(1,'zhangsan'),(2,'lisi'); COMMIT;

?

1 2 3 4 5 6 7

--打开两个mysql窗口A,B A:set autocommit=0; A:select * from tab_no_index where id = 1; B:set autocommit=0 B:select * from tab_no_index where id =2; A:select * from tab_no_index where id = 1 for update; B:select * from tab_no_index where id = 2 for update;--出现阻塞等待

?

1	alter table mylock add PRIMARY KEY (`id`);

　　共享锁(S锁)：允许多个事务去读一行，阻止事务获取排他锁，加锁的方式：SELECT .. LOCK IN SHARE MODE
　　排他锁(X锁)：允许一个事务获取排他锁，阻止其他事务获取共享锁和排他锁，加锁的方式：SELECT .. FOR UPDATE(INSERT,DELETE)
　　间隙锁：是对锁的补充定义，对于符合加锁条件的数据，在完锁后，查询或修改的数据，使其满足条件，重新加锁，可以防止幻读。
　　查询锁竞争情况：

?

1	show status like 'innodb_row_lock%';

MVCC多版本并发控制：
　　是在读已提交（生成语句级快照）和可重复读隔离级别（生成事务级快照）下起作用，在读取数据的时候生成版本号，等到其他事务commit之后，产生新的版本号，当事务A读取了记录（生成版本号），事务B修改记录，事务A再次读取时，会根据最新的版本号读取，如果事务B还没有提交，事务A读取之前的版本号数据。
　　主要是通过read view 和undo log来实现的，undo log记录了事务前的数据，而read view是在查询时，InnoDB会生成一个read view，read view有几个重要字段，分别是：trx_ids(尚未提交的事务版本号集合)，low_limit_id(下一次要生成的事务ID值)，low_limit_id（尚未提交版本号的事务ID最小值）以及creator_trx_id(当前的事务版本号)，在每行数据有两列隐藏的字段，分别是DB_TRX_ID（记录着当前ID）以及DB_ROLL_PTR（指向上一个版本数据在undo log 里的位置指针），read commit隔离级别下，每次都获取一个新的read view，repeatable read隔离级别则每次事务只获取一个read view。