MYISAM表锁

 MYISAM存储引擎只支持表锁，并且不支持事物。
 MySQL的表级锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）。
 注意：只有读锁和读锁是兼容的，其他的组合都不兼容,需要等待（这里有个特例，当满足并发插入条件时，INSERT和SELECT操作是可以并行执行的）。

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

和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！根据如表20-2所示的 例子可以知道，当一个线程获得对一个表的写锁后，只

有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

 实验一、MYISAM写阻塞读的例子
 

 session1session2 --创建表mysql> create table film_text(file_id int,    ->        title varchar(20),primary key(file_id)    ->    )engine=myisam;Query OK, 0 rows affected (0.04 sec)mysql> insert into film_text values(1001,'update test');Query OK, 1 row affected (0.00 sec)  --获得file_text表的write锁mysql>  lock table film_text write;Query OK, 0 rows affected (0.00 sec) --当前的会话对表的查询、更新、插入操作都可以执行mysql> select * from film_text where file_id=1001;+---------+-------------+| file_id | title       |+---------+-------------+|    1001 | update test |+---------+-------------+1 row in set (0.00 sec)                                                            --session2对锁定表的查询被阻塞，需要等待锁被释放（以便获得表级读锁）mysql> select * from film_text where file_id=1001;mysql> insert into film_text values(1003,'test');Query OK, 1 row affected (0.00 sec)mysql> update film_text set title='test' where file_id=1001;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0--释放锁mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)                                                             --session2获得读锁，查询返回+---------+-------+| file_id | title |+---------+-------+|    1001 | test  |+---------+-------+1 row in set (1 min 25.25 sec)

 说明：MYISAM在执行查询语句（select）前，会自动给设计的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给设计的表

加写锁，这个过程 并不需要用户手动干预，因此，用户一般不需要直接用lock table命令给myisam表显示加锁。例子中，显示加锁是为了方便说明问

题，并非必须如此。

实验二、MYISAM存储引擎读阻塞写的例子

 session1session2  --获得film_text的READ锁mysql> lock table film_text read;Query OK, 0 rows affected (0.00 sec)  --session1可以查询该表的记录mysql> select * from film_text where file_id=1001;+---------+-------+| file_id | title |+---------+-------+|    1001 | test  |+---------+-------+1 row in set (0.00 sec)                                                --session2也可以查询该表的记录mysql> select * from film_text where file_id=1001;+---------+-------+| file_id | title |+---------+-------+|    1001 | test  |+---------+-------+1 row in set (0.00 sec)--当前会话不能查询没有锁定的表mysql> select * from t1 where id=3;ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES --session2可以查询或修改未锁定的表mysql> select * from t1 where id=3;+----+------+| id | name |+----+------+|  3 | sss  |+----+------+1 row in set (0.00 sec)mysql> update t1 set name='huang' where id=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0  --当前会话更新或更新锁定的表都会提示错误mysql> insert into film_text values(1004,'huang');ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql> update film_text set title='Test' where file_id=1001;ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated --session2更新锁定表会等待锁mysql> update film_text set title='Test' where file_id=1001;等待 --释放锁mysql> unlock tables;Query OK, 0 rows affected (0.00 sec) --session2获得锁，更新操作完成Query OK, 1 row affected (1 min 5.49 sec)Rows matched: 1  Changed: 1  Warnings: 0

实验三、并发插入实验

对于MYISAM引擎，一般来说写阻塞读，读阻塞写，但是在满足并发插入的条件时，写和读是可以并发的。
注意：这里所说的并发插入是指的在满足条件的情况下，INSERT和SELECT操作是可以并发的。

1、和并发查询相关的参数（什么情况下可以查询和插入并行，下面的参数说明里讲的很清楚）

concurrent_insert 

Command-Line Format--concurrent_insert[=#]System VariableNameconcurrent_insertVariable ScopeGlobalDynamic VariableYesPermitted ValuesTypeenumerationDefaultAUTOValid ValuesNEVERAUTOALWAYS012

If AUTO (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you startmysqld with --skip-new, this variable is set to NEVER.

This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.

ValueDescriptionNEVER (or 0)Disables concurrent insertsAUTO (or 1)(Default) Enables concurrent insert for MyISAM tables that do not have holesALWAYS (or 2)Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires anormal write lock and inserts the row into the hole.

2、并发插入实验（写不阻塞读）

看了实验一有人就会有疑问了，他遇到的有的情况，写不阻塞读。确实，当满足并发插入的条件的情况下，写是不阻塞读的。
2.1、下面例子给一个写不阻塞读的例子（满足并发插入条件）

session1                                                       session2mysql> drop table if exists film_text;Query OK, 0 rows affected (0.00 sec)mysql> create table film_text(file_id int,     -> title varchar(20),primary key(file_id)    -> )engine=myisam;Query OK, 0 rows affected (0.04 sec)mysql> insert into film_text values(1001,'test');Query OK, 1 row affected (0.00 sec)mysql> insert into film_text values(1002,sleep(10));                                                               mysql> select * from film_text where file_id=1001;                                                               +---------+-------+                                                               | file_id | title |                                                               +---------+-------+                                                               |    1001 | test  |                                                               +---------+-------+                                                               1 row in set (0.00 sec)Query OK, 1 row affected, 1 warning (10.00 sec)

说明：从上面这个实验看出在满足并发插入条件下，insert操作是不持有写锁的。

2.2、下面例子给一个写阻塞读的例子（不满足并发插入条件）

session1                                                       session2mysql> drop table if exists film_text;Query OK, 0 rows affected (0.00 sec)mysql> create table film_text(file_id int,     -> title varchar(20),primary key(file_id)    -> )engine=myisam;Query OK, 0 rows affected (0.05 sec)mysql> insert into film_text values(1001,'test'),(1002,'test2'),    -> (1003,'test3'),(1004,'test4'),(1005,'test5'),(1006,'test6');Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> delete from film_text where file_id<1005;Query OK, 4 rows affected (0.00 sec)mysql> insert into film_text values(1002,sleep(10));                                                            select * from film_text where file_id=1005;                                                            等待Query OK, 1 row affected, 1 warning (10.00 sec)                                                            +---------+-------+                                                            | file_id | title |                                                            +---------+-------+                                                            |    1005 | test5 |                                                            +---------+-------+                                                            1 row in set (9.33 sec)

3、并发插入实验（读不阻塞写）

注意：查询时无法使用sleep函数长时间持有锁，数据库检索到需要的数据后就释放锁。
看了实验二有人就会有疑问了，他遇到的有的情况，读不阻塞写。和上面一样，当满足并发插入的条件的情况下，写是不阻塞读的。

3.1、下面例子给一个读不阻塞写的例子（满足并发插入条件）

session1                                                       session2mysql> drop table if exists film_text;Query OK, 0 rows affected (0.00 sec)mysql> create table film_text(file_id int not null AUTO_INCREMENT,     -> title varchar(20),primary key(file_id)    -> )engine=myisam;Query OK, 0 rows affected (0.05 sec)mysql> insert into film_text(title) values('test test test test');Query OK, 1 row affected (0.01 sec)....重复执行上面的插入语句.....mysql> insert into  film_text(title) select title from film_text;Query OK, 8388608 rows affected, 1 warning (17.70 sec)Records: 8388608  Duplicates: 0  Warnings: 1mysql> select * from film_text where title like '%rrrrr%';                                                                 mysql> insert into film_text(title) values('test');                                                                 Query OK, 1 row affected (0.00 sec)                                                                 没有等待Empty set (4.63 sec)

3.2、下面例子给一个读阻塞写的例子,接着上面的测试（不满足并发插入条件）

session1                                                       session2mysql> delete from film_text where file_id<1000000;Query OK, 999999 rows affected (9.42 sec)mysql> select * from film_text where title like '%rrrrr%';                                                               mysql> insert into film_text(title) values('test');                                                               等待Empty set (4.44 sec)                                                               Query OK, 1 row affected (3.81 sec)

检测系统上表锁争用情况

mysql> show status like 'table%';+-----------------------+----------+| Variable_name         | Value    |+-----------------------+----------+| Table_locks_immediate | 59539047 || Table_locks_waited    | 2676     |+-----------------------+----------+2 rows in set (0.00 sec)

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语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲

突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

需要注意

1、在LOCK TABLES时加上“LOCAL”选项，其作用就是在满足MYISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。
2、在调用LOCAL TABLES 给表显示加锁时，必须同时取得所有涉及表的锁，并且MYSQL不支持锁升级。也就是说，在执行LOCK TABLES
   后,只能访问显示加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那只能执行查询操作，而不是执行更新操作。在
   自动加锁的情况下也是如此，MYISAM总是一次性获得SQL语句需要的全部锁。着也正是MYISAM表不会出现死锁的原因。
3、当LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表的SQL语句中出现多次，就要通过与SQL语句中相同的别名锁定

   多少次，否则会出错。