MySQL 锁机制

<pre name="code" class="html">MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。1、MyISAM 表锁可以通过检查table_locks_waited 和table_locks_immediate 状态变量来分析系统上的表锁定争夺：mysql> show status like 'table%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Table_locks_immediate | 78    || Table_locks_waited    | 0     |+-----------------------+-------+2 rows in set (0.00 sec)如果Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。MySQL 的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM 表的读操作与写操作之间，以及写操作之间是串行的！mysql> lock table film_text write;mysql> lock table film_text read;mysql> unlock tables;在用LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL 不支持锁升级。也就是说，在执行LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM 总是一次获得SQL 语句所需要的全部锁。这也正是MyISAM 表不会出现死锁（Deadlock Free）的原因。当使用LOCK TABLES 时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL 语句中出现多少次，就要通过与SQL 语句中相同的别名锁定多少次，否则也会出错！##并发插入（Concurrent Inserts）MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。mysql> show variables like '%concurrent_insert%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| concurrent_insert | 1     |+-------------------+-------+1 row in set (0.00 sec)? 当concurrent_insert设置为0时，不允许并发插入。? 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。? 当concurrent_insert设置为2时，无论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就暂时将写请求的优先级降低，给读进程一定获得锁的机会。2、InnoDB 行锁InnoDB 与MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。可以通过检查InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：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     |+-------------------------------+-------+5 rows in set (0.00 sec)如果发现锁争用比较严重，如InnoDB_row_lock_waits 和InnoDB_row_lock_time_avg 的值比较高，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。InnoDB 实现了以下两种类型的行锁。? 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。? 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。? 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS 锁。? 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX 锁。事务可以通过以下语句显示给记录集加共享锁或排他锁。? 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。? 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。##InnoDB 行锁实现方式InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点MySQL 与Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！由于MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL 通过判断不同执行计划的代价来决定的，如果MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB 将使用表锁，而不是行锁。##间隙锁（Next-Key 锁）当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。很显然，在使用范围条件检索并锁定记录时，InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。还要特别说明的是，InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁！