并发控制和锁机制

锁概念

锁的分类

  • 读锁：又称共享锁、S锁，所有事务(人)只能读不能写 并且多个读互不阻塞
  • 写锁：又称独占锁、排它锁、X 锁，除了当前事务外 会阻塞其它事务的读和写

锁的粒度

  • 表级锁：MyISAM
  • 行级锁：InnodB

锁的实现

  • 存储引擎：自行实现其锁策略和锁粒度（由存储引擎自动施加锁）
  • 服务器级：实现了锁，表级锁，用户可显式请求（用户手动请求）

锁策略

  • 在锁粒度及数据安全性寻求的平衡机制

锁的实现

  • 这里指的是手动实现加锁

表加锁

语法

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...

#lock_type: 
READ
WRITE

范例

加读锁

  • 总结：只对加锁的表生效，任何终端对所有表的读操作不受任何影响 但增删改操作无法进行，在其他终端同样进行修改和插入内容都进入到阻塞状态
#表加读锁前
mysql> insert students (name,age) VALUES ('james',16);
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
...
26 rows in set (0.00 sec)

#加读锁
mysql> LOCK TABLES students READ;
Query OK, 0 rows affected (0.00 sec)

#表加读锁后
mysql> insert students (name,age) VALUES ('azheng',18);
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated
mysql> select * from students;
...
26 rows in set (0.00 sec)

加写锁

  • 总结：只对加锁的表生效，当前终端的任何操作不受影响，其它终端会阻塞增删改查
#表加写锁前
mysql> insert students (name,age) VALUES ('azheng',18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
...
27 rows in set (0.00 sec)

#加写锁
mysql> LOCK TABLES students WRITE;
Query OK, 0 rows affected (0.00 sec)

#表加写锁后
#当前终端读写正常
mysql> insert students (name,age) VALUES ('azheng',23);
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
...
28 rows in set (0.00 sec)
#其他终端针对加锁的表均卡住不动，等待锁释放才能成功执行
mysql> select * from teachers;
...
4 rows in set (0.01 sec)
mysql> select * from students; #执行后会一直卡住不动，直到解锁
mysql> insert students (name,age) VALUES ('azheng',23); #执行后会一直卡住不动，直到解锁

解锁

  • 解全部当前会话的锁，加锁的终端关闭的话同时也会解锁
UNLOCK TABLES

加全局读锁

  • 关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

语法

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

范例

  • **总结：**加全局读锁后，对所有库、所有表皆可查询，但不能对任何表和库进行增删改
#在当前终端加全局读锁
FLUSH TABLES WITH READ LOCK;

#当前终端
------------------------------------------------------------------------------
#当前终端查询不受阻塞
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  66 | M      |
|   2 | Zhang Sanfeng |  66 | M      |
|   3 | Miejue Shitai |  88 | F      |
|   4 | Lin Chaoying  |  66 | F      |
|   5 | xiangzheng    |  23 | M      |
|   6 | xiaohong      |  17 | F      |
+-----+---------------+-----+--------+

#当前终端修改阻塞
mysql> update teachers  set age='19' where tid=6;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

#当前终端插入阻塞
mysql> insert teachers (name,age,gender) value ('xiaoqiang',26,'M');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

#当前终端创建新的数据库阻塞
mysql> create database abc;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
------------------------------------------------------------------------------


#其他终端
------------------------------------------------------------------------------
#其他终端查询不受阻塞
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  66 | M      |
|   2 | Zhang Sanfeng |  66 | M      |
|   3 | Miejue Shitai |  88 | F      |
|   4 | Lin Chaoying  |  66 | F      |
|   5 | xiangzheng    |  23 | M      |
|   6 | xiaohong      |  17 | F      |
+-----+---------------+-----+--------+

#其他终端修改阻塞
mysql> update teachers  set age='19' where tid=6;

#其他终端插入阻塞
mysql> insert teachers (name,age,gender) value ('xiaoqiang',26,'M');

#其他终端创建新的数据库阻塞
mysql> create database abc;
------------------------------------------------------------------------------


#解锁后立刻可以进行写操作
UNLOCK TABLES;

查询时加写或读锁

  • 使用较少

语法

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

范例: 同时在两个终端对同一行记录修改

  • 由存储引擎自动加的锁
#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0