MySql的锁详解

一、锁的概念

1.  锁是计算机协调多个进程或线程并发访问某一资源的机制。
2. 在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
3. 锁对数据库而言显得尤其重要，也更加复杂。

二、MyISAM引擎的锁

       mysam引擎只有表级锁：

       1.共享读锁

序号	session1	session2
1	lock table testmysam READ
2	select * from testmysam   （可以查询）	select * from testmysam   （可以查询）
3	insert into testmysam value(2); （报错）
4	update testmysam set id=2  where id=1;（报错）
5		insert into testmysam value(2); （阻塞）
6	insert into account value(4,'aa',123); （报错）
7	select  * from account  ; （报错）
8		insert into account value(4,'aa',123); （成功）
9	select s.* from  testmysam s （报错）

总结：在session1对表testmysam加读锁之后，session1，只能对testmysam表进行读操作，不能进行写操作，也不能对其他表进行任何操作；其他session可以对表testmysam进行读操作，写操作会被阻塞，但是其他session可以对其他表操作。

     2.独占写锁

序号	session1	session2
1	lock table testmysam WRITE
2	insert testmysam value(3);（成功）
3	delete from testmysam where id = 3 （成功）
4	select * from testmysam （成功）
5	select s.* from  testmysam s （报错）
6	insert into account value(4,'aa',123);  （报错）
7		select * from testmysam （阻塞）
8		insert testmysam value(3);（阻塞）
		insert into account value(4,'aa',123); （成功）

总结：在session1对表testmysam加写锁之后，session1，只能对testmysam表进行任何操作，但不能对其他表进行任何操作；其他session对表testmysam的任何操作都会被阻塞，但是其他session可以对其他表操作。

 

二、InnoDB引擎的锁

mysql的InnoDB引擎支持行锁，行锁分为共享锁（读锁）和 排他锁（写锁）

共享锁又称：读锁。

当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。读锁可以共存。

排它锁又称：写锁。

当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。写锁不和任何锁共存。

上共享锁的写法：lock in share mode

例如： select  *  from tableA  where  。。。lock in share mode；

上排它锁的写法：for update

例如：select *  from tableA  where 。。。for update；

 

注意：

1.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

2.insert ，delete ， update在事务中都会自动默认加上排它锁。

3.两个事务不能锁同一个索引。

4.提交事务，回滚事务，新开起一个事务都会释放行锁。

InnoDB的行锁演示

CREATE TABLE testdemo (
`id`  int(255) NOT NULL ,
`c1`  varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`c2`  int(50) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `idx_c2` (`c2`) USING BTREE 
)
ENGINE=InnoDB;

序号	session1	session2
1	BEGIN select * from testdemo where id =1 for update  在id为1的记录上加读锁
2		update testdemo set c1 = '1' where id = 2 （成功）
3		update testdemo set c1 = '1' where id = 1（ 等待）
	结论：行锁只能锁住互译韩记录，不影响其他记录。
4	BEGIN update testdemo set c1 = '1' where id = 1
		update testdemo set c1 = '1' where id = 1（ 等待）
	结论：修改记录的操作自动加行锁。
5	BEGIN update testdemo set c1 = '1' where  c1 = '1'
6		update testdemo set c1 = '2' where  c1 = '2' （ 等待）
	结论：行锁是基于索引实现的，更新条件没有索引时，会锁表。
7	select * from testdemo where id =1 for update
		select * from testdemo where id =1 lock in share mode
8	UNLOCK TABLES  并不会解锁 使用commit 或者 begin或者ROLLBACK 才会解锁
	结论：.提交事务，回滚事务，新开起一个事务都会释放行锁

锁等待问题

你肯定碰到过这问题，有些程序员在debug程序的时候，经常会锁住一部分数据库的数据，而这个时候你也要调试这部分功能，却发现代码总是运行超时，你是否碰到过这问题了，其实这问题的根源我相信你也知道了。

举例来说，有两个会话。

程序员甲，正直调试代码

BEGIN

      SELECT * FROM testdemo WHERE id = 1 FOR UPDATE

你正直完成的功能也要经过那部分的代码，你得上个读锁

BEGIN

      SELECT * FROM testdemo WHERE id = 1 lock in share mode

这个时候很不幸，你并不知道发生了什么问题，在你调试得过程中永远就是一个超时得异常，而这种问题不管在开发中还是在实际项目运行中都可能会碰到，那么怎么排查这个问题呢？

这其实也是有小技巧的。

select * from information_schema.INNODB_LOCKS;

我通过这个sql语句起码发现在同一张表里面得同一个数据有了2个锁其中一个是X（写锁），另外一个是S（读锁）

解决：

select * from sys.innodb_lock_waits

执行的这个sql语句看下最下面，kill命令，你在工作中完全可以通过kill吧阻塞了的sql语句给干掉，你就可以继续运行了。

如果是MySQL5.6

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread
FROM
  information_schema.innodb_lock_waits w
INNER JOIN
  information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
  information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

kill 掉阻塞的线程即可。