能查不能写
解锁
无法更新数据
释放锁
更新数据
表级锁
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到正无穷也加了锁
因为是大于取大于的正无穷