mysql以表级锁_MySQL中的表级锁

数据的锁主要用来保证数据的一致性，数据库的锁从锁定的粒度上可以分为表级锁，行级锁和页级锁。

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

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也是最高

页面锁：开销和加锁时间结余表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发多一般。

查看你的MySQL数据支持哪些存储引擎

mysql> show engines;

mysql>show engines;+------------+---------+------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

5 rows in set (0.00 sec)

MyISAM表锁

MyISAM存储引擎只支持表锁，MySQL的表级锁有两种模式：表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。

对于读操作，可以增加读锁，一旦数据表被加上读锁，其他请求可以对该表再次增加读锁，但是不能增加写锁。(当一个请求在读数据时，其他请求也可以读，但是不能写，因为一旦另一个线程写了数据，就会导致读取到的数据不是最新的了，这就是不可重复读现象)

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

读锁和写锁都是阻塞锁。

如果t1对数据表增加了写锁，这时t2请求对数据表增加写锁，这时候t2并不会直接返回，而是会一直处于阻塞状态，知道t1释放了对表的锁，这时t2遍有可能加锁成功，获取到结果。

表级锁的加锁/解锁方式

MyISAM在执行查询语句(SELECT)前，会自动给设计的所有表加读锁，在执行更新操作(Update，Delete，Insert等)前，会自动给所涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

MyISAM表的读操作和写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有所的线程可以对表进行更新操作。其他线程的读，写操作都会等待，知道锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁清秋，然后再给读锁队列中等候的获取锁请求。这也正是MyISAM表不太适合有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死”，应用中应尽量避免出现长时间运行的查询操作(在可能的情况下可以通过使用中间表等措施对SQL语句做一定的分解，使每一步都能在较短的时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段进行，比如一些定期统计可以安排在夜间执行)。

可以设置改变读锁和写锁的优先级：

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

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

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

给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后MySQL就暂时将写请求的优先级降低，给读今次那个一定获得锁的机会。

在自动加锁的情况下，MyISAM总是一次获得SQL语句所需要的全部锁，这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量。

如果你使用LOCK TABLES显式获取表锁，则可以请求READ LOCAL锁而不是READ锁，以便在锁定表时，其他会话可以使用并发插入。

当concurrent_insert设置为0时，不允许并发插入。

当concurrent_insert设置为1时，如果MyISAM表中没有空洞(即表的中间没有被删除的行)，MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。

当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁的争夺，如果table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况：

mysql> SHOW STATUS LIKE 'Table%';+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 48595 |

| Table_locks_waited | 44 |

+-----------------------+-------+

2 rows in set (0.00 sec)