MySQL之锁

MySQL 刘宇帅 4年前 阅读量: 910

什么是锁

在计算机科学中,锁是在执行多线程是强制限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。

MySQL的锁

MySQL 个存储引擎从锁定范围上有三种级别的锁:

  1. 表级锁定 表锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
  2. 行级锁定 行锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
  3. 页级锁定 页锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

InnoDB

InnoDB 默认使用行级锁,InnoDB 的行锁是通过锁定索引实现的,只有通过索引条件检索数据才会加行锁或则使用表锁。
InnoDB 行锁分为共享锁和排他锁 共享锁:同一个资源可以加多个共享锁,但是不能加排他锁 排他锁:排他锁不能和任何其他锁共存
InnoDB 使用意向锁实现表锁的概念分为意向共享锁和意向意向排他锁,当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务需要在锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个,但是意向排他锁同时只能有一个存在。 InnoDB 锁如何添加

  1. 意向锁是InnoDB自动添加的,不需要人为添加
  2. 对于 UPDATE、DELETE 和 INSERT 语句,InnoDB 会自动给涉及数据集加排他锁
  3. 事务可以通过以下语句显示给记录集加共享锁或排他锁:
    共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
    排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE

间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

死锁

InnoDB 的事务的锁是逐步获得的,当两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。
InnoDB 的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在, InnoDB 发现死锁之后,会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多,在死锁中就越不会被回滚掉。

检查行锁的争用情况

通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 50637 |
| Innodb_row_lock_time_avg      | 50637 |
| Innodb_row_lock_time_max      | 50637 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.01 sec)
InnoDB_row_lock_current_waits:当前正在等待锁定的数量。
InnoDB_row_lock_time:从系统启动到现在锁定总时间长度。
InnoDB_row_lock_time_avg:每次等待所花平均时间。
InnoDB_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间。
InnoDB_row_lock_waits:系统启动后到现在总共等待的次数。

检查死锁、解锁

查询是否锁表

mysql> show OPEN TABLES where In_use > 0;
Empty set (0.02 sec)

查询进程

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info             |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 430476 | Waiting on empty queue | NULL             |
| 13 | root            | localhost | NULL | Query   |      0 | starting               | show processlist |
| 14 | root            | localhost | NULL | Sleep   |   4062 |                        | NULL             |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)

杀死进程

mysql> kill 14;
Query OK, 0 rows affected (0.00 sec)

查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

提示

功能待开通!


暂无评论~