Mysql表锁、行锁、锁机制解析

在前面的 数据库事务原理 中提到了为什么需要使用事务的原因 , 现如今主流数据库都引入了锁机制 ,并引入了事务隔离级别的概念 , 本文就Mysql的各种常见锁进行解析.

一 . 锁种类(按锁级别)

        MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB 存储引擎采用的是页面锁(page-level locking)，但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。

• 表级锁:开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁:开销大，加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低，并发度也最高。
• 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

1 . 表级别锁

        MySQL 的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。其实无论是表锁还是行锁。共享锁指的就是读锁！互斥锁、排他锁、独占锁值得都是写锁。读锁和写锁都是阻塞锁

表级别读锁

• 申请读锁的前提是当前没有线程对该表使用写锁，否则该语句会被阻塞。申请读锁成功后，其他线程也可以对该表进行读操作，但不允许有线程对其进行写操作，就算是当前线程也不允许。当锁住了A表之后，就只能对A表进行读操作，对其他表进行读操作会出现错误（tablename was not locked with LOCK TABLES）
• 当一个请求在读数据时，其他请求也可以读，但是不能写，因为一旦另外一个线程写了数据，就会导致当前线程读取到的数据不是最新的了。这就是不可重复读现象）

表级别写锁

对于写操作，可以增加写锁，一旦数据表被加上写锁，其他请求无法在对该表增加读锁和写锁。（当一个请求在写数据时，其他请求不能执行任何操作，因为在当前事务提交之前，其他的请求无法看到本次修改的内容。这有可能产生脏读、不可重复读和幻读）

表锁的加锁/解锁方式

MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT 等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。而MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。

如果用户想要显示的加锁可以使用以下命令：

## 对表 a 加读锁：
LOCK TABLES a READ

## 解锁
UNLOCK a

## 对表 a 增加写锁
LOCK TABLES a WRITE

## 解锁
UNLOCK a

当使用 LOCK TABLES 时,不仅需要一次锁定用到的所有表,而且,同一个表在 SQL 语句中出现多少次,就要通过与 SQL 语句中相同的别名锁定多少次,否则也会出错!

select a1.* , b1.*  from a as a1 , a as b1 where a1.cc = b1.cc 

// 比如上面这句 ,同一张表 a , 在同一条sql中出现了两次 , 分别是a1 和 b1 ,

// 那么 在进行加表锁的时候 ,sql 应该如下

lock table a as a1  read , a as b1 read ;

1 . 行级别锁

• 行级锁是Mysql中锁定粒度最细的一种锁，能大大减少数据库操作的冲突，由于其粒度小，加锁的开销最大。
• INNODB的行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。共享锁允许事物读一行记录，不允许任何线程对该行记录进行修改。排他锁允许当前事物删除或更新一行记录，其他线程不能操作该记录。
• 行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

 共享锁：

• 共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
• 如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

    用法： SELECT ... LOCK IN SHARE MODE;

    MySQL会对查询结果集中每行都添加共享锁。

    锁申请前提：当前没有线程对该结果集中的任何行使用排他锁，否则申请会阻塞。

 排他锁：

        排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

      用法： SELECT ... FOR UPDATE;

       MySQL会对查询结果集中每行都添加排他锁，在事物操作中，任何对记录的更新与删除操作会自动加上排他锁。

     锁申请前提：当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。        

MyISAM的锁调度

前面讲过,MyISAM 存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个 MyISAM 表的读锁,同时另一个进程也请求同一表的写锁,MySQL 如何处理呢?

答案是写进程先获得锁。

不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为 MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!

幸好我们可以通过 一些设置来调节 MyISAM 的调度行为。

通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。

通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

另外,MySQL也 供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count 设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低, 给读进程一定获得锁的机会。