MySQL进阶之MySQL中的锁( 二 ) _MySQL

共享锁（S），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；排他锁（X），允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
-- 使用LOCK IN SHARE MODE手动添加共享锁（S）SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;-- 使用FOR UPDATE手动添加排他锁（x）SELECT * FROM table_name WHERE condition FOR UPDATE;使用COMMIT或ROLLBACK语句释放锁。
查看行级锁争用状态
mysql> show status like 'innodb_row_lock%';+-------------------------------+-------+| Variable_name| Value |+-------------------------------+-------+| Innodb_row_lock_current_waits | 0|| Innodb_row_lock_time| 0|| Innodb_row_lock_time_avg| 0|| Innodb_row_lock_time_max| 0|| Innodb_row_lock_waits| 0|+-------------------------------+-------+其中第2、3、5项是优化或分析问题的比较常用指标
Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
【MySQL进阶之MySQL中的锁】Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
行锁基本演示
使用2个会话分别连接MySQL服务器，首先使用会话1更新tdep表中id为3的记录，其中id为主键，有索引，所以会自动为该记录加上排他锁（X），示例中关闭了自动提交，以验证行锁的效果。

文章插图
会话1的表现
此时会话2可以更新其它记录，也可以读取id为3的记录，但更新id为3的记录将被阻塞

文章插图
其它会话的表现
行读锁（共享锁）演示
使用会话1为tdep表中id为3的记录加上共享锁，基本上可以做任何的操作

文章插图
会话1加行读锁的表现
会话2可以访问其它表的数据以及未锁定的其它行，id为3的行可以读取但不能更新

文章插图
其它会话的表现
行写锁（排他锁）演示
会话1对tdep表中id为3的记录加了排他锁，基本上可以做任何的操作

文章插图
会话1加行写锁的表现
会话2可以访问tdep表，但不能该记录进行更新或删除，同时也不能对该记录进行加共享锁或排他锁。

文章插图
其它会话的表现

表级锁和行级锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
总结关于InnoDB的行级锁，这里主要介绍了记录锁。InnoDB行级锁是通过给索引实现的，只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将自动使用表锁。
对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及数据记录加排他锁（X)，这意味着其它会话不能再对这些记录进行INSERT、UPDATE、DELETE操作以及使用LOCK IN SHARE MODE或FOR UPDATE对涉及的数据记录进行加读锁或写锁，非要操作将被阻塞；
对于普通SELECT语句，InnoDB不会加任何锁，但可以通过LOCK IN SHARE MODE或FOR UPDATE语句显示给记录集加共享锁或排他锁。
一条记录被加了排他锁之后，其它会话不能再对该记录加锁，包括共享锁和排他锁。但如果一条记录加的是共享锁，则其它会话可以对该记录加共享锁，但不能加排他锁。即一条记录可以同时被多个会话加共享锁，但只能被一个会话加排他锁。
不同于表的独占写锁，行的排他锁可以允许其它会话读取行记录，而表的独占写锁则不允许其它会话读取表中的记录。即行级锁作用是限制了其它会话对加锁的记录进行增删改，但不限制读取。