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的记录。
举手之劳,不要吝惜您的赞!-_-
网友评论