mysql的锁表解锁处理

在mysql中，最常出现的就是锁表，也是见的最多的，其实在mysql中的锁机制也分表级锁，页面锁和行锁。三者的区别在于存储引擎，表级锁(table-level locking)就是采用MyISAM和MEMORY存储引擎，页面锁（page-level locking)是用BDB存储引擎，行级锁(row-level locking)是InnoDB存储引擎，表级锁，页面锁和行级锁都支持表级锁，行级锁默认采用行级锁。

下面看个例子，针对锁表，表锁后就不能进行增删改的操作，只能读。表级锁开销会小，但是也会减少吞吐量。

 mysql> show tables;
 +----------------+
 | Tables_in_test |
 +----------------+
 | DEPT           |
 | EMP            |
 | SALGRADE       |
 | person         |
 | place          |
 | student        |
 | test_test      |
 | wulaoer        |
 +----------------+
 8 rows in set (0.00 sec)
 ​
 mysql> lock table EMP read;   #锁表
 Query OK, 0 rows affected (0.01 sec)
 ​
 mysql> select * from EMP;
 +-------+--------+-----------+------+------------+---------+---------+--------+
 | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
 +-------+--------+-----------+------+------------+---------+---------+--------+
 |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
 |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
 |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
 |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
 |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
 +-------+--------+-----------+------+------------+---------+---------+--------+
 14 rows in set (0.01 sec)
 ​
 mysql> show processlist;  #查看锁表
 +----+------+-----------+------+---------+------+----------+------------------+
 | Id | User | Host      | db   | Command | Time | State    | Info             |
 +----+------+-----------+------+---------+------+----------+------------------+
 |  2 | root | localhost | test | Query   |    0 | starting | show processlist |
 +----+------+-----------+------+---------+------+----------+------------------+
 1 row in set (0.00 sec)
 ​
 mysql> insert into EMP ( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) values (666,'wulaoer','IT',8999,'1988-02-28',9000,200,30);  #插入数据失败
 ERROR 1099 (HY000): Table 'EMP' was locked with a READ lock and can't be updated
 mysql> show processlist;
 +----+------+-----------+------+---------+------+----------+------------------+
 | Id | User | Host      | db   | Command | Time | State    | Info             |
 +----+------+-----------+------+---------+------+----------+------------------+
 |  2 | root | localhost | test | Query   |    0 | starting | show processlist |
 +----+------+-----------+------+---------+------+----------+------------------+
 1 row in set (0.00 sec)
 ​
 mysql> unlock tables;    #解锁
 Query OK, 0 rows affected (0.00 sec)
 ​
 mysql> insert into EMP ( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) values (666,'wulaoer','IT',8999,'1988-02-28',9000,200,30);
 Query OK, 1 row affected (0.01 sec)
 ​
 mysql> select * from EMP;
 +-------+---------+-----------+------+------------+---------+---------+--------+
 | EMPNO | ENAME   | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
 +-------+---------+-----------+------+------------+---------+---------+--------+
 |   666 | wulaoer | IT        | 8999 | 1988-02-28 | 9000.00 |  200.00 |     30 |
 |  7369 | SMITH   | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
 |  7499 | ALLEN   | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
 |  7521 | WARD    | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
 |  7566 | JONES   | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
 |  7654 | MARTIN  | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
 |  7698 | BLAKE   | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
 |  7782 | CLARK   | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
 |  7788 | SCOTT   | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
 |  7839 | KING    | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
 |  7844 | TURNER  | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
 |  7876 | ADAMS   | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
 |  7900 | JAMES   | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
 |  7902 | FORD    | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
 |  7934 | MILLER  | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
 +-------+---------+-----------+------+------------+---------+---------+--------+
 15 rows in set (0.00 sec)

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

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

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

上面使用的show processlist的详解如下：

 id       #ID标识，要kill一个语句的时候很有用
 use      #当前连接用户
 host     #显示这个连接从哪个ip的哪个端口上发出
 db       #数据库名
 command  #连接状态，一般是休眠（sleep），查询（query），连接（connect）
 time     #连接持续时间，单位是秒
 state    #显示当前sql语句的状态
 info     #显示这个sql语句

其中state的状态十分关键：

 Checking table  正在检查数据表（这是自动的）。
 Closing tables  正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
 Connect Out 复制从服务器正在连接主服务器。
 Copying to tmp table on disk  由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
 Creating tmp table  正在创建临时表以存放部分查询结果。
 deleting from main table  服务器正在执行多表删除中的第一部分，刚删除第一个表。
 deleting from reference tables  服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
 Flushing tables 正在执行FLUSH TABLES，等待其他线程关闭数据表。
 Killed  发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
    
 Locked  被其他查询锁住了。
    
 Sending data  正在处理SELECT查询的记录，同时正在把结果发送给客户端。
 Sorting for group 正在为GROUP BY做排序。
 Sorting for order 正在为ORDER BY做排序。
 Opening tables  这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
 Removing duplicates 正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
    
 Reopen table  获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
 Repair by sorting 修复指令正在排序以创建索引。
 Repair with keycache  修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
 Searching rows for update 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
 Sleeping  正在等待客户端发送新请求.
 System lock 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
 Upgrading lock  INSERT DELAYED正在尝试取得一个锁表以插入新记录。
 Updating  正在搜索匹配的记录，并且修改它们。
 User Lock 正在等待GET_LOCK()。
 Waiting for tables  该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
 waiting for handler insert  INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

以上是真对表锁和解锁的内容，没有了看些其他的吧～～～～～～～～～～～～～～