锁机制
MYISAM在查询时会默认加上读锁，插入时默认加上写锁
读锁 加上后 其他线程可读不可写
写锁加上后 别的线程不能读不能写，要等持锁的session释放锁
表锁一般用在查询为主，只有少量按索引条件更新数据的应用，行锁更适合有大量按索引并发更新少量（因为行锁很耗资源，大量并发更新，会使得资源开销很大）不同数据，又有并发查询的应用
MySQL中的表锁兼容性
| 请求锁模式 是否兼容当前锁模式 | None | 读锁 | 写锁 |
|---|---|---|---|
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中，显式加锁基本上都是为了方便而已，并非必须如此。
给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：
1. Select sum(total) from orders; 2. Select sum(subtotal) from order_detail; 3. 这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是： 4. Lock tables orders read local, order_detail read local; 5. Select sum(total) from orders; 6. Select sum(subtotal) from order_detail; 7. Unlock tables;
要特别说明以下两点内容。
- 上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关MyISAM表的并发插入问题，在后面的章节中还会进一步介绍。
- 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。
上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
- 当concurrent_insert设置为0时，不允许并发插入。
- 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
- 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
在如下表所示的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session_2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞。
MyISAM也支持并发插入
MyISAM存储引擎的读写（INSERT）并发例子
可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。
MyISAM的锁调度
前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！****这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。
- 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
- 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。
InnoDB查看行锁的竞争状态
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
1. mysql> show status like 'innodb_row_lock%';
2. +-------------------------------+-------+
3. | Variable_name | Value |
4. +-------------------------------+-------+
5. | InnoDB_row_lock_current_waits | 0 |
6. | InnoDB_row_lock_time | 0 |
7. | InnoDB_row_lock_time_avg | 0 |
8. | InnoDB_row_lock_time_max | 0 |
9. | InnoDB_row_lock_waits | 0 |
10. +-------------------------------+-------+
11. 5 rows in set (0.01 sec)
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
1. 具体方法如下：
2. mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
3. Query OK, 0 rows affected (0.14 sec)
4. 然后就可以用下面的语句来进行查看：
5. mysql> Show innodb status\G;
6. *************************** 1. row ***************************
7. Type: InnoDB
8. Name:
9. Status:
10. …
11. …
12. \------------
13. TRANSACTIONS
14. \------------
15. Trx id counter 0 117472192
16. Purge done for trx's n:o < 0 117472190 undo n:o < 0 0
17. History list length 17
18. Total number of lock structs in row lock hash table 0
19. LIST OF TRANSACTIONS FOR EACH SESSION:
20. ---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456
21. MySQL thread id 200610, query id 291197 localhost root
22. ---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936
23. MySQL thread id 199285, query id 291199 localhost root
24. Show innodb status
25. …
26. 监视器可以通过发出下列语句来停止查看：
27. mysql> DROP TABLE innodb_monitor;
28. Query OK, 0 rows affected (0.05 sec)设置监视器后，在SHOW INNODB STATUS的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“--console”选项来启动服务器以关闭写日志文件。
InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
在如下所示的例子中，开始tab_no_index表没有索引：
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
在如下表所示的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
在下面的例子中，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。
例子中tab_with_index表的name字段有索引，但是name字段是varchar类型的，如果where条件中不是和varchar类型进行比较，则会对name进行类型转换，而执行的全表扫描。
间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其它事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
在如下表所示的例子中，假如emp表中只有101条记录，其empid的值分别是1,2,......,100,101。
InnoDB存储引擎的间隙锁阻塞例子
| session_1 | session_2 |
|---|---|
| mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec) | mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec) |
| 当前session对不存在的记录加for update的锁：mysql> select * from emp where empid = 102 for update;Empty set (0.00 sec) | |
| 这时，如果其他session插入empid为102的记录（注意：这条记录并不存在），也会出现锁等待：mysql>insert into emp(empid,...) values(102,...);阻塞等待 | |
| Session_1 执行rollback：mysql> rollback;Query OK, 0 rows affected (13.04 sec) | |
| 由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录：mysql>insert into emp(empid,...) values(102,...);Query OK, 1 row affected (13.35 sec) |
explain select * from prod
