Mysql的锁

共享锁和排它锁

shared lock 和 exclusive lock (共享锁和排它锁，也叫读锁和写锁，即read lock和write lock

共享锁【S锁/读锁】

又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。
这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁【X锁/写锁】

又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

范围锁

表锁

锁类型	当前session	其他session
读锁	可读不可写 不可读其他的表	可读 写阻塞
写锁	可读可写 不可读其他的表	读写阻塞

-- 加读锁
lock table [table_name] write/read
-- 解锁
unlock tables

行锁

-- 加共享锁
SELECT * FROM [table_name]] WHERE id=1 LOCK IN SHARE MODE;
-- 加排他锁
SELECT * FROM [table_name]] WHERE id=1 FOR UPDATE;

无索引导致行锁升级为表锁

session1 开启事务，修改 test_innodb_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set a=44 where b=4000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据
由于发生了自动类型转换，索引失效，导致行锁变为表锁

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='9001' where a=9;
# 在这儿阻塞着呢~~~

其他锁

间隙锁

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

间隙锁的危害

• 因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

间隙锁示例

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+

session1 开启事务，执行修改 a > 1 and a < 6 的数据，这会导致 mysql 将 a = 2 的数据行锁住（虽然表中并没有这行数据）

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='Heygo' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据，也会导致阻塞，直至 session1 提交事务

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9;
# 在这儿阻塞着呢~~~

锁的粒度和锁的策略

锁级别	表锁	行锁	页锁
粒度	大	小	中
开销	小	大	中
速度	快	慢	中
是否出现死锁	否	是	是
发生锁冲突概率	高	低	中
并发度	低	高	中
存储引擎	InnoDB BDB MyISAM	InnoDB	BDB

表锁分析与优化

分析

-- myisam分析
show status like 'table%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Table_locks_immediate      | 500440 |
| Table_locks_waited         | 1      |
+----------------------------+--------+


-- innodb分析
show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 212969 |
| Innodb_row_lock_time_avg      | 42593  |
| Innodb_row_lock_time_max      | 51034  |
| Innodb_row_lock_waits         | 5      |
+-------------------------------+--------+

• MyiSAM分析
  1. Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
  2. Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；
• InnoDB分析
  1. Innodb_row_lock_current_waits：当前正在等待锁定的数量；
  2. Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
  3. Innodb_row_lock_time_avg：每次等待所花平均时间；
  4. Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
  5. Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

优化

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离