1.支持

MYSQL锁 官方文档

首先需要明确的是,对于MySQL而言,不同的engine所支持的锁类型不一样:MyISAM仅支持表锁,BDB支持表锁和页锁,InnoDB支持表锁和行锁;

| Engine| 表锁|行锁|页锁|
|-|-|-|
|MyISAM|√|ㄨ|ㄨ|
|BDB|√|ㄨ|√|
|InnoDB|√|√|ㄨ|

需要额外说明的是,对数据库的连接丢失或断开将会丢失该进程生成的所有锁,当进程重连时也不会生效.

If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect.

2.表锁

表锁指的是:
在单个进程中锁表后,若锁定模式为WRITE,则允许该进程读写表,但是只允许其他进程的读表操作,写表操作将会被阻塞,直到该锁被释放;
若锁定模式为READ,则允许所有进程的读表操作,当前进程的写表操作将被拒绝,其他进程的写表操作将会被阻塞,直到该锁被释放.

write-lock demo:

// 进程1
mysql> lock table `test` write;
// 大约十秒后释放写锁
mysql> unlock tables;

// 进程2
mysql> insert into `test`(`id`) values ('9');
Query OK, 1 row affected (10.74 sec)

read-lock demo:

// 进程1
mysql> lock table `test` read;
// 此时进行写表将会被拒绝
mysql> insert into `test`(`id`) values ('7');
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated

// 同上,大约十秒后释放读锁
mysql> unlock tables;

// 进程2
mysql> insert into `test`(`id`) values ('4');
Query OK, 1 row affected (10.33 sec)

3.事务

官方文档

事务是一组连续的操作集合,它需要SET AUTOCOMMIT = 0;(默认为1),以START TRANSACTION;或BEGIN;开始,以COMMIT;结束一次事务,或者以ROLLBACK;回滚事务操作并结束事务.

实际上,在AUTOCOMMIT为1的情况下,每一次请求都是一个独立的事务,即该次请求会被立即执行;

需要注意的是,```SET AUTOCOMMIT与START TRANSACTION在功能上可能相同,但是在概念上完全不同.前者表示是否开启自动提交(0 = 否, 1 = 是),后者表示开始一个事务,这一点要区分开来,不能因为功能满足而混淆概念. 根据文档说明,在SET AUTOCOMMIT = 1的前提下开启事务时,执行COMMIT或ROLLBACK前AUTOCOMMIT``都会是默认禁用状态,在事务结束后会恢复默认状态:

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

事务最显著的特点是:拥有独立的内部逻辑,事务中的请求与操作在执行COMMIT;前都不会对数据库该事务外产生任何影响;

demo:

// 进程1
// 开启一个事务,直到COMMIT前以下语句都不会在该事务外对数据库产生影响
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`(`id`) values ('1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `test`;
+----+---------------------+
| id | Created_time        |
+----+---------------------+
| 1  | 1970-01-01 08:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

// 进程2
mysql> SELECT * FROM `test`;
Empty set (0.00 sec)

我们可以看到,由于进程1开启了一个事务,所以进程1中的INSERT在其事务中可以查询到改变,但是在事务外的进程2中并不能.
那么我们把进程1的事务COMMIT以后:

// 进程1
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

// 进程2
mysql> SELECT * FROM `test`;
+----+---------------------+
| id | Created_time        |
+----+---------------------+
| 1  | 1970-01-01 08:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

这个处理流式有点类似于git,而ROLLBACK则可以理解为git checkout .或git reset --hard上一版本.

需要额外说明的是,如果事务结束前,进程断开或丢失连接,事务将会被强制回滚,并且重连后进程的AUTOCOMMIT将会是可用状态.

In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled.

所以官方文档建议可以适时关闭auto-reconnect功能以获取错误信息并及时修正原本的事务;

With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction.

自动重连行为控制官方文档

4.行锁

之所以把行锁放到事务后面说明,是因为行锁引用到一些事务的概念.

行锁与表锁不同的是,行锁在Mysql中不是针对数据加锁,而是针对索引加的锁,所以在不同的访问请求中如果使用了相同的索引,还是会出现冲突.

我们在单次事务中,可能需要在本次事务运行过程中阻止其它事务对目标数据的修改,但是使用表锁会锁定整个表,阻塞一大堆不相干的事务和进程,这时就需要针对目标数据进行锁定.InnoDB提供了for update来做行锁;

DEMO:

// 事务1
// 开始执行一个事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

// 锁定id = 1的数据行,有效期为本事务生命周期
mysql> select * from `test` where `id`='1' for update;
+----+---------------------+
| id | Created_time        |
+----+---------------------+
| 1  | 1970-01-01 08:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

// 执行所需的操作
mysql> update `test` set `id`='9' where `id`='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
// 不结束事务,此时观察其它事务的情况

// 事务2
mysql> update `test` set `id`='9' where `id`='1';

发现为事务1未结束前,事务2的update操作一直处于被阻塞的状态.
并且事务2在长时间的Lock等待后,会直接抛出错误信息

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

那么问题来了,事务1更新了数据后是否会影响到事务2的索引结果呢?
我们把事务1COMMIT后观察一下:

// 事务1
COMMIT;

// 事务2
mysql> update `test` set `id`='5' where `id`='1';
Query OK, 0 rows affected (8.20 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM `test` where `id`='1';
+----+---------------------+
| id | Created_time        |
+----+---------------------+
| 9  | 1970-01-01 08:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

显然,事务2被事务1的行锁阻塞解除后重新进行了一次索引,因此没有匹配到任何值,这可以有效避免高并发情况下多事务处理时可能出现的脏读.

关于数据库事务隔离级别

脏读(Dirty reads (Uncommitted Dependency))
当一个事务允许读取另外一个事务修改但未提交的数据时，就可能发生脏读(dirty reads)。

脏读(dirty reads)和不可重复读(non-repeatable reads)类似。事务2没有提交造成事务1的语句1两次执行得到不同的结果集。在未提交读(READ UNCOMMITTED)隔离级别唯一禁止的是更新混乱，即早期的更新可能出现在后来更新之前的结果集中。