(1). 前言
(2). 锁是什么?
锁主要是用来解决多线程之间,并发访问同一共享资源而带来的数据安全问题.虽然,锁能解决数据安全问题,但是,也会带来性能的影响.
(3). 表锁/行锁/间隙锁
表锁:对整张表进行加锁,写操作互斥,读取操作正常.
行锁:对行数据进行加锁,写操作互斥,读取操作正常.
间隙锁:这种锁是介于行锁与表锁之间的.
(4). 表锁(读锁)
当对某个线程对表进行上读锁时,其余的线程只允许读,不允许写.
# 事务一:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table departments read;
Query OK, 0 rows affected (0.00 sec)
# 事务二:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.00 sec)
# 尝试去写,发现会阻塞在此处
mysql> INSERT INTO departments(dept_no,dept_name) VALUES('d009','IT');
# 事务一,释放当前会话的锁,事务二会继续往下执行.
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 事务二,会在事务一,释放锁后,继续往下执行.
mysql> INSERT INTO departments(dept_no,dept_name) VALUES('d009','IT');
ERROR 1062 (23000): Duplicate entry 'd009' for key 'PRIMARY'
(5). 表锁(写锁)
当某个线程,对表进行上写锁时,其余的线程不允许读和写.
# 事务一,对departments表开启写锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table departments write;
Query OK, 0 rows affected (0.00 sec)
# 事务二,查询:departments表
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 事务二,一直阻塞在这里.
mysql> SELECT * FROM departments;
# 中断请求.
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
# 事务二,尝试加进行写操作,也会一直阻塞在这里.
mysql> INSERT INTO departments(dept_no,dept_name) VALUES('d0010','IT');
(6). 行锁
当某个线程给某行数据加锁之后,其它线程是写(W)操作是互斥的,而读(R)操作是允许的.
# 事务一对表departments加行锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM departments WHERE dept_no = 'd009' FOR UPDATE;
+---------+------------------+
| dept_no | dept_name |
+---------+------------------+
| d009 | Customer Service |
+---------+------------------+
1 row in set (0.00 sec)
# 事务二,更新departments表的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 一直阻塞在此处,然后,抛出异常
mysql> UPDATE departments SET dept_name = 'Customer Service 2' WHERE dept_no = 'd009';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
# 事务二,可以读取表中所有数据.
mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
(7). 死锁
mysql> use employees;
# 1.
mysql> begin;
# 2. 对数据行:d010加锁.
mysql> UPDATE departments SET dept_name = 'IT-1' WHERE dept_no = 'd010';
# 3. 尝试对d011行数据加锁,结果阻塞在此处.
mysql> UPDATE departments SET dept_name = 'IT-2.1' WHERE dept_no = 'd011';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> use employees;
# 1.
mysql> begin;
# 2. 对数据行:d011加锁.
mysql> UPDATE departments SET dept_name = 'IT-2.1' WHERE dept_no = 'd011';
mysql> UPDATE departments SET dept_name = 'IT-1' WHERE dept_no = 'd010';