MySQL行级锁、表级锁、页级锁详细介绍

页级（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

表级（table-level）

• 直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作，可以同时读。
• 如果你是写锁，则其它进程则读也不允许，
• 表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
• 使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

行级（row-level）

• 单独的一行记录加锁 。
• 行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
• 虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
• 使用行级锁定的主要是InnoDB存储引擎

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

注意：

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，因为mysql优化器发现：即使使用了索引，还是要做全表扫描，故而放弃了索引，也就没有使用行锁，却使用了表锁。 可以理解为MySQL不确定要扫描的范围时，优化器直接表锁，表锁所花的代价比行锁更优。以上问题即便你使用了 force index 强制索引，结果还是一样，永远都是表锁。**

 update table set num=1 where id>50

表级锁模式

• 表共享读锁（Table Read Lock）

  不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

• 表独占写锁（Table Write Lock）

  会阻塞其他用户对同一表的读和写操作；

---

行级锁定

• 共享锁

  共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

• 排他锁

  排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
  mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁.
  共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE;
  排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE;

注：

对于排他锁大家的理解可能就有些差别，排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。不是锁住一行数据后，其他事务就不能读取和修改该行数据

select name from class where id in (select id from use where id <5) for update; 
select name from class where id in (1,2,3,4,5) for update;    

大部分会认为结果一样没什么区别，其实差别大了，区别就是第一条 sql 语句会产生表锁，而第二个 sql 语句是行锁，为什么呢？因为第一个 sql 语句用了子查询外围查询故而没使用索引，导致表锁。