mysql锁
2022-02-07 本文已影响0人
喏喏2021
1. 相关概念
按锁的粒度分: 表锁、页锁、行锁
行锁是锁表粒度最小、最细的一种锁，能大大减少数据库冲突的概率，当然锁表的开锁也是最大的
表锁是粒度最大的一种锁，是对整张表进行加锁，是对整张表进行加锁，不会出现死锁的情况，但锁冲突的概率较大，常用的InnoDB引擎支持行锁和表锁
页级锁是粒度介于行锁和表锁之间，BDB引擎支持页锁
行级锁按使用方式分：共享锁、排它锁
共享锁：也叫读锁，或是S锁, 数据使用共享锁后，不能对数据进行修改，其它事务也只能使用共享锁，而不能使用排它锁
排它锁：也叫写锁、独占锁，或是X锁，使用排它锁的事务，可以读也可以写，其它事务不能使用共享锁或是排它锁
行级锁按类型分：记录锁、间隔锁、临键锁、插入意向锁
记录锁Record Lock，锁住特定的记录，当然如果没有查询到记录时，就会上升到表锁
间隔锁Gap Lock，就是在索引的间隙上加上锁，这里是实现防止可重复读的主要原因，是一个左开右开的区间
临键锁Next-key Lock，就是记录锁+间隔锁，是一个左开右闭的区间
插入意向锁Insert Intention Lock，是一种间隔锁，会和间隔锁及临键锁发生冲突，以阻止其他插入操作执行，以提高并发插入的性能
聚簇索引、非聚簇索引
聚簇索引：查找的数据就在索引中，比如主键，或第一个唯一键，或数据库内部维护的行ID
非聚簇索引：查找的数据不在索引中，一般为普通二级索引，真实数据还需要从聚簇索引中获取
2. 与锁相关的参数
锁等待的时间
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
缺省为50秒，
mysql> set innodb_lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 5 |
+--------------------------+-------+
1 row in set (0.00 sec)
这里我们将缺省时间调整了5秒
当前是否有锁表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| my_order | goods | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
这里我们可以看，goods被锁住了
当前正在锁的表
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 196181479:385:3:2 | 196181479 | X | RECORD | `my_order`.`goods` | PRIMARY | 385 | 3 | 2 | 1 |
| 196181478:385:3:2 | 196181478 | X | RECORD | `my_order`.`goods` | PRIMARY | 385 | 3 | 2 | 1 |
+-------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)
可以看出，goods表有两条锁记录，模式是排他锁，是记录锁
等待锁的表
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+-------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+-------------------+
| 196181479 | 196181479:385:3:2 | 196181478 | 196181478:385:3:2 |
+-------------------+-------------------+-----------------+-------------------+
1 row in set (0.00 sec)
这个表可以看到一条记录，一个是当前请求事务ID，还是阻塞中的事务ID
查看详细的运行时信息
mysql> show engine innodb status\G;
LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 4926, OS thread handle 0x6318, query id 1728630 localhost ::1 root statistics
select * from goods where id=1 for update
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 385 page no 3 n bits 80 index `PRIMARY` of table `my_order`.`goods` trx id 196181479 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
上面是截取了一小段，这里可以看出锁表的语句
超时相关的参数
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
超时相关的参数，包括前面的innodb，锁超时时间
3. 示例
间隔锁示例
1）初始表中数据
初始表数据.png
id为主键索引，num为非唯一索引
2）事务1锁表
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from goods where num=5 for update;
+----+-----------+------+---------------------+---------------------+---------------+
| id | name | num | ctime | utime | desc |
+----+-----------+------+---------------------+---------------------+---------------+
| 3 | 小米汽车3 | 5 | 2022-01-30 22:14:49 | 2022-01-30 22:14:49 | 添加小米汽车3 |
| 4 | 小米汽车3 | 5 | 2022-01-30 22:20:36 | 2022-01-30 22:20:36 | 添加小米汽车3 |
| 5 | 小米汽车3 | 5 | 2022-01-30 22:38:49 | 2022-01-30 22:38:49 | 添加小米汽车3 |
+----+-----------+------+---------------------+---------------------+---------------+
3 rows in set (0.00 sec)
这里语句是对num=5进行锁定，因为缺省是可重复读隔离级别，实际这里是间隔锁，一个是<5，还有一个是（5，31）共两段
3）事务2插入num=2记录
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into goods(name,num) values('mi',2);
Query OK, 1 row affected (29.77 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
事务2在插入num=2时，会进入阻塞等待状态，等待事务1提交
4）事务1提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from goods;
+----+-----------+------+---------------------+---------------------+----------------+
| id | name | num | ctime | utime | desc |
+----+-----------+------+---------------------+---------------------+----------------+
| 1 | 小米汽车 | 31 | 2022-01-29 10:51:32 | 2022-01-30 22:38:49 | addNum方法更新 |
| 3 | 小米汽车3 | 5 | 2022-01-30 22:14:49 | 2022-01-30 22:14:49 | 添加小米汽车3 |
| 4 | 小米汽车3 | 5 | 2022-01-30 22:20:36 | 2022-01-30 22:20:36 | 添加小米汽车3 |
| 5 | 小米汽车3 | 5 | 2022-01-30 22:38:49 | 2022-01-30 22:38:49 | 添加小米汽车3 |
| 6 | mi | 2 | 2022-02-07 16:34:15 | 2022-02-07 16:34:15 | NULL |
+----+-----------+------+---------------------+---------------------+----------------+
5 rows in set (0.00 sec)
事务1进行提交，事务2再提交后，再查询一下，看到了刚插入的num=2的记录。
举手之劳，不要吝惜您的赞！-_-
