面试官问，谈谈mysql的锁，我一口气说了11把mysql“锁”

# 加读锁
 
 Lock table tablename read;
 
#释放锁
 
unlock table;
 
案例如下 ：
 
#session1 给表创建读锁（创建一个连接就是一个回话session）
 
mysql> lock table userinfo read;
 
Query OK, 0 rows affected (0.01 sec)
 
#可以读取当前表的数据
 
mysql> select * from userinfo ;
 
+----+----------+------+
 
| id | name     | age  |
 
+----+----------+------+
 
|  1 | zhangsan |   27 |
 
|  2 | lisi     |   27 |
 
|  3 | dd       | NULL |
 
|  4 | dd       |    2 |
 
+----+----------+------+
 
4 rows in set (0.00 sec)
 
#session1  因为给userinfo表加了读锁，所以不能读取其他表数据
 
mysql> select * from student_score;
 
ERROR 1100 (HY000): Table 'student_score' was not locked with LOCK TABLES
 
#session1  因为给表加的读锁，所以当前回话不能对表进行其他操作，
 
mysql> update userinfo set age=age+1;
 
ERROR 1099 (HY000): Table 'userinfo' was locked with a READ lock and can't be updated
 
  
 
#session2，在session1 没有释放锁之前，session2可以进行读取userinfo
 
mysql> select * from userinfo;
 
+----+----------+------+
 
| id | name     | age  |
 
+----+----------+------+
 
|  1 | zhangsan |   27 |
 
|  2 | lisi     |   27 |
 
|  3 | dd       | NULL |
 
|  4 | dd       |    2 |
 
+----+----------+------+
 
4 rows in set (0.00 sec)
 
#session2，在session1 没有释放锁之前，session2修改或删除表userinfo的任何数据都被阻塞
 
mysql> update  userinfo set age=1 where name='dd';
 
。。。。一直等待，直到seesion1 释放锁
 
#session1 释放锁,session1在执行下面释放锁语句后，session2才会提交上面修改语句。
 
mysql> unlock tables;

# 加写锁给表
 
 Lock table tablename write;
 
# 加写锁给行
 
 select ... for update;
 
#释放表锁
 
unlock table;
 
案例如下 ：
 
# session1 当前会话session1给表加写锁。
 
mysql> lock table userinfo write;
 
Query OK, 0 rows affected (0.00 sec)
 
#session1 当前会话可以修改此表数据
 
mysql> update userinfo set age=9;
 
Query OK, 4 rows affected (0.01 sec)
 
Rows matched: 4  Changed: 4  Warnings: 0
 
#session1  当前会话可以查询此表
 
mysql> select * from userinfo;
 
+----+----------+------+
 
| id | name     | age  |
 
+----+----------+------+
 
|  1 | zhangsan |    9 |
 
|  2 | lisi     |    9 |
 
|  3 | dd       |    9 |
 
|  4 | dd       |    9 |
 
+----+----------+------+
 
4 rows in set (0.00 sec)
 
#session2，在session1 没有释放锁之前，session2对表的任何数据任何操作（增删改查）userinfo的任何数据都被阻塞
 
mysql> update  userinfo set age=1 where name='dd';
 
。。。。一直等待，直到seesion1 释放锁
 
  
 
如上是锁表，接下来我们看看给行加锁的效果：
 
   #修改提交模式，改为手动提交
 
mysql> set autocommit=0;
 
Query OK, 0 rows affected (0.00 sec)
 
#session1 当前会话给id=1的行加写锁。
 
mysql>  select * from userinfo where id=1 for update;
 
+----+----------+------+
 
| id | name     | age  |
 
+----+----------+------+
 
|  1 | zhangsan |   10 |
 
+----+----------+------+
 
1 row in set (0.00 sec)
 
#session2 当前会话2 可以查询当前表数据。
 
mysql> select * from userinfo;
 
+----+----------+------+
 
| id | name     | age  |
 
+----+----------+------+
 
|  1 | zhangsan |   11 |
 
|  2 | lisi     |   12 |
 
|  3 | dd       |   12 |
 
|  4 | dd       |   11 |
 
+----+----------+------+
 
4 rows in set (0.00 sec)
 
#session2 当前会话2 可以操作其他行数据。
 
mysql> update userinfo set age=12 where id=2;
 
Query OK, 0 rows affected (0.01 sec)
 
Rows matched: 1  Changed: 0  Warnings: 0
 
#session2 当前会话2修改数据涉及到id=1的行，所以被阻塞知道行锁被释放
 
mysql> update userinfo set age=12 ;
 
。。。。一直被阻塞，直到sesssion1 执行commit语句。
 
#session1 回话1 提交数据，也就是释放锁
 
mysql> commit;
 
Query OK, 0 rows affected (0.00 sec)

伪业务代码如下：
 
#首先获取修改之前的数据和版本号
 
SELECT data AS old_data, version AS old_version FROM …;
 
#进行修改，条件是当前数据的版本号和之前的老版本号一致，表示没有人进行过修改
 
Int updaterow= UPDATE SET data = new_data, version = new_version WHERE version = old_version
 
// 表示修改成功，拿到了乐观锁
 
if (updated row > 0) {
 
// 乐观锁获取成功，操作完成
 
} else {
 
// 乐观锁获取失败，回滚并重试
 
}

#关闭自动提交后
 
set autocommit=0;
 
#开始事务
 
 start transaction;
 
#查询当前商品信息，然后通过for update锁定数据防止其他事务修改
 
select status  from goods where goodsid=1 for update;
 
#根据商品信息生成订单
 
insert into orders (id,goodsid) values (null,1);
 
#修改商品status为2
 
update t_goods set status=2;
 
#执行完毕,提交事务
 
commit;

#session1 操作会话session1 开启事务
 
mysql> begin;
 
Query OK, 0 rows affected (0.00 sec)
 
#session1 更新id=11的数据，获取行锁。
 
mysql> select * from userinfo where id=11  for update;
 
Empty set (0.00 sec)
 
  
 
#session2 操作会话session2 开启事务
 
mysql> begin;
 
Query OK, 0 rows affected (0.00 sec)
 
#session2 在会话session2 新增数据主键为12
 
mysql> insert into userinfo values(12,'hh',18);
 
.... 一直处于阻塞状态
 
#如果等待时间过长，session1没有释放锁，会抛出如下异常。
 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select * from userinfo where id=1 for update;
 
delete from userinfo where id=4;
 
insert into userinfo values(4,'hh',18);
 
阻塞时间过长可能出抛出如下错误：
 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如下在事务中执行。
 
Update userinfo SET age=19 where id= 10;
 
Select * from userinfo where id=10 FOR UPDATE;

#查看正在被锁的事务
 
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
 
#可以通过如下指令，杀死当前事务进程，释放锁。trx_id字段对应上面语句结果对应的第一个字段值
 
kill trx_id;