7. MYSQL 锁

能查不能写

解锁

无法更新数据

释放锁

更新数据

表级锁

mysql> lock tables score read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from score;
+----+------+------+---------+---------+
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   67 |      88 |      95 |
|  2 | Rose |   23 |      66 |      90 |
|  3 | Jack |   56 |      98 |      76 |
+----+------+------+---------+---------+
3 rows in set (0.00 sec)

mysql> update score set math=100 where id=2;
ERROR 1099 (HY000): Table 'score' was locked with a READ lock and can't be updated
当前客户端增加了读的锁无法更新
在另一个客户端进行更新会被堵塞

进行解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

1.表共享读锁(read lock)

客户端加锁后 所有客户端只能读不能写

2.表独占写锁(write lock)

客户端加此锁后 只有当前客户端可以写 其他客户端不可读写

客户端1加锁
mysql> lock tables score write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from score;
+----+------+------+---------+---------+
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   67 |      88 |      95 |
|  2 | Rose |   23 |      66 |      90 |
|  3 | Jack |   56 |      98 |      76 |
+----+------+------+---------+---------+
3 rows in set (0.00 sec)

mysql> update score set chinese = 100 where id =2;
Query OK, 1 row affected (0.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

客户端2无法读取数据 也无法更新/写入数据


客户端1解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.11 sec)

客户端2可查询
mysql> select * from score;
+----+------+------+---------+---------+
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   67 |      88 |      95 |
|  2 | Rose |   23 |      66 |      90 |
|  3 | Jack |   56 |      98 |      76 |
+----+------+------+---------+---------+
3 rows in set (0.00 sec)

元数据锁

客户端1开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from score;
+----+------+------+---------+---------+
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   67 |      88 |      95 |
|  2 | Rose |   23 |      66 |     100 |
|  3 | Jack |   56 |      98 |      76 |
+----+------+------+---------+---------+
3 rows in set (0.00 sec)

客户端2执行修改元数据修改操作 发现无法执行 因为他的锁会跟其他所有的锁都排次
mysql> alter table score add column java int;
提交事务                                                                                                   
mysql> commit
    -> ;
Query OK, 0 rows affected (0.04 sec)
客户端2修改成功
mysql> alter table score add column java int;
Query OK, 0 rows affected (2 min 40.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

查询元数据锁

每次执行操作所需要的锁都会放到这个表中

客户端1开启事务后查询数据
mysql> select *  from score where id= 1 lock in share mode;
+----+------+------+---------+---------+------+
| id | name | math | english | chinese | java |
+----+------+------+---------+---------+------+
|  1 | Tom  |   67 |      88 |      95 | NULL |
+----+------+------+---------+---------+------+
1 row in set (0.00 sec)

在客户端2查询锁
mysql>  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db1           | score       | NULL       | TABLE     | IS            | NULL      |
| db1           | score       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.11 sec)


- `object_schema`：锁定对象所属的数据库模式（schema）。
- `object_name`：锁定对象的名称。
- `index_name`：锁定对象所使用的索引名称。
- `lock_type`：锁定的类型，可以是表级别的锁（TABLE）或记录级别的锁（RECORD）。
- `lock_mode`：锁定的模式，描述了锁定的具体方式。例如，IS表示共享锁（Intent Share），S表示共享锁（Share）。
- `lock_data`：锁定的数据，根据锁定类型和模式的不同，可能是记录的标识符或其他相关数据。

在给定的示例中，有两个锁定行：
- 第一行表示对名为`score`的表进行了一个表级别的IS锁定。
- 第二行表示对名为`score`表的`PRIMARY`索引中的一个记录进行了一个记录级别的S锁定，锁定的记录标识符为1。

在客户端2增加
mysql> lock tables score read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update score set math = 66 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
  mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db1           | score       | NULL       | TABLE     | IX            | NULL      |
| db1           | score       | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+

行级锁

间隙锁

锁住 锁住一个范围

共享锁 可以给很多个线程同时使用的锁 读锁

排他锁只能给一个线程用的锁 需要 写锁

mysql>  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db1           | score       | NULL       | TABLE     | IS            | NULL      |
| db1           | score       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

mysql>      
mysql> 
mysql> select * from score where id = 1 lock in share mode;
+----+------+------+---------+---------+------+
| id | name | math | english | chinese | java |
+----+------+------+---------+---------+------+
|  1 | Tom  |   66 |      88 |      95 | NULL |
+----+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql>  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db1           | score       | NULL       | TABLE     | IS            | NULL      |
| db1           | score       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
| db1           | score       | NULL       | TABLE     | IS            | NULL      |
| db1           | score       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |   
+---------------+---------

无法执行成功因为
客户端1中给 id为1的行增加了共享锁 共享锁无法写入数据
mysql> update score set math = 100 where id =1;

没有索引导致变成了表所

增加索引后 就变成了行所 在另一个客户端执行的写入语句不是表锁的部分发现可以更新
mysql> create index idx_score_name on score(mach);
ERROR 1072 (42000): Key column 'mach' doesn't exist in table
mysql> create index idx_score_name on score(math);

Query OK, 0 rows affected (1.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> update score set math = 300 where math = 200;
Query OK, 0 rows affected (0.70 sec)
Rows matched: 0  Changed: 0  Warnings: 0

开启一个事务

在事务中更新一个在一个范围的的id数据

查询表发现表已经加上了间隙锁

在客户机2中事务插入一个数据 无法插入 因为间隙锁已经把1到8这个区间给锁住了

客户机1提交事务

客户机2锁归还可插入

在查询发现已经插入

间隙锁把跟新的区间给锁住 防止 数据插入导致逻辑错误

创建了索引

加锁

3,3 这条记录加了行锁

在将3前面的间隙锁住

7,7的间隙也锁住

25也锁和之前的间隙也锁

25到正无穷也加了锁

因为是大于取大于的正无穷