Mysql可以并发写吗 mysql 并发问题

转载

lanhy 2024-08-11 11:35:11

文章标签 Mysql可以并发写吗 php mysql 线程安全 mysql i++ 数据 文章分类 MySQL 数据库

在并行系统中并发问题永远不可忽视。尽管PHP语言原生没有提供多线程机制，那并不意味着所有的操作都是线程安全的。尤其是在操作诸如订单、支付等业务系统中，更需要注意操作数据库的并发问题。接下来我通过一个案例分析一下PHP操作数据库时并发问题的处理问题。

首先，我们有这样一张数据表：

mysql> select * from counter;
+----+-----+
| id | num |
+----+-----+
|  1 | 0 |
+----+-----+
1 row in set (0.00 sec)

这段代码模拟了一次业务操作：

function dummy_business() {
$conn = mysqli_connect('127.0.0.1', 'public', 'public') or die(mysqli_error());
mysqli_select_db($conn, 'test');
for ($i = 0; $i < 10000; $i++) {
mysqli_query($conn, 'UPDATE counter SET num = num + 1 WHERE id = 1');
}
mysqli_close($conn);
}
for ($i = 0; $i < 10; $i++) {
$pid = pcntl_fork();
if($pid == -1) {
die('can not fork.');
} elseif (!$pid) {
dummy_business();
echo 'quit'.$i.PHP_EOL;
break;
}
}
?>

上面的代码模拟了10个用户同时并发执行一项业务的情况，每次业务操作都会使得num的值增加1，每个用户都会执行10000次操作，最终num的值应当是100000。

运行这段代码，num的值和我们预期的值是一样的：

mysql> select * from counter;
+----+--------+
| id | num  |
+----+--------+
|  1 | 100000 |
+----+--------+
1 row in set (0.00 sec)

这里不会出现问题，是因为单条UPDATE语句操作是原子的，无论怎么执行，num的值最终都会是100000。然而很多情况下，我们业务过程中执行的逻辑，通常是先查询再执行，并不像上面的自增那样简单：

function dummy_business() {
$conn = mysqli_connect('127.0.0.1', 'public', 'public') or die(mysqli_error());
mysqli_select_db($conn, 'test');
for ($i = 0; $i < 10000; $i++) {
$rs = mysqli_query($conn, 'SELECT num FROM counter WHERE id = 1');
mysqli_free_result($rs);
$row = mysqli_fetch_array($rs);
$num = $row[0];
mysqli_query($conn, 'UPDATE counter SET num = '.$num.' + 1 WHERE id = 1');
}
mysqli_close($conn);
}
for ($i = 0; $i < 10; $i++) {
$pid = pcntl_fork();
if($pid == -1) {
die('can not fork.');
} elseif (!$pid) {
dummy_business();
echo 'quit'.$i.PHP_EOL;
break;
}
}
?>

改过的脚本，将原来的原子操作UPDATE换成了先查询再更新，再次运行我们发现，由于并发的缘故程序并没有按我们期望的执行：

mysql> select * from counter;
+----+------+
| id | num  |
+----+------+
|  1 | 21495|
+----+------+
1 row in set (0.00 sec)

入门程序员特别容易犯的错误是，认为这是没开启事务引起的。现在我们给它加上事务：

function dummy_business() {
$conn = mysqli_connect('127.0.0.1', 'public', 'public') or die(mysqli_error());
mysqli_select_db($conn, 'test');
for ($i = 0; $i < 10000; $i++) {
mysqli_query($conn, 'BEGIN');
$rs = mysqli_query($conn, 'SELECT num FROM counter WHERE id = 1');
mysqli_free_result($rs);
$row = mysqli_fetch_array($rs);
$num = $row[0];
mysqli_query($conn, 'UPDATE counter SET num = '.$num.' + 1 WHERE id = 1');
if(mysqli_errno($conn)) {
mysqli_query($conn, 'ROLLBACK');
} else {
mysqli_query($conn, 'COMMIT');
}
}
mysqli_close($conn);
}
for ($i = 0; $i < 10; $i++) {
$pid = pcntl_fork();
if($pid == -1) {
die('can not fork.');
} elseif (!$pid) {
dummy_business();
echo 'quit'.$i.PHP_EOL;
break;
}
}
?>

依然没能解决问题：

mysql> select * from counter;
+----+------+
| id | num  |
+----+------+
|  1 | 16328|
+----+------+
1 row in set (0.00 sec)

请注意，数据库事务依照不同的事务隔离级别来保证事务的ACID特性，也就是说事务不是一开启就能解决所有并发问题。通常情况下，这里的并发操作可能带来四种问题：

更新丢失：一个事务的更新覆盖了另一个事务的更新，这里出现的就是丢失更新的问题。

脏读：一个事务读取了另一个事务未提交的数据。

不可重复读：一个事务两次读取同一个数据，两次读取的数据不一致。

幻象读：一个事务两次读取一个范围的记录，两次读取的记录数不一致。

通常数据库有四种不同的事务隔离级别：

隔离级别

脏读

不可重复读

幻读

Read uncommitted
√
√
√
Read committed
×
√
√
Repeatable read
×
×
√
Serializable
×
×
×

大多数数据库的默认的事务隔离级别是提交读(Read committed)，而MySQL的事务隔离级别是重复读(Repeatable read)。对于丢失更新，只有在序列化(Serializable)级别才可得到彻底解决。不过对于高性能系统而言，使用序列化级别的事务隔离，可能引起死锁或者性能的急剧下降。因此使用悲观锁和乐观锁十分必要。并发系统中，悲观锁(Pessimistic Locking)和乐观锁(Optimistic Locking)是两种常用的锁：

悲观锁认为，别人访问正在改变的数据的概率是很高的，因此从数据开始更改时就将数据锁住，直到更改完成才释放。悲观锁通常由数据库实现(使用SELECT...FOR UPDATE语句)。

乐观锁认为，别人访问正在改变的数据的概率是很低的，因此直到修改完成准备提交所做的的修改到数据库的时候才会将数据锁住，完成更改后释放。

上面的例子，我们用悲观锁来实现：

function dummy_business() {
$conn = mysqli_connect('127.0.0.1', 'public', 'public') or die(mysqli_error());
mysqli_select_db($conn, 'test');
for ($i = 0; $i < 10000; $i++) {
mysqli_query($conn, 'BEGIN');
$rs = mysqli_query($conn, 'SELECT num FROM counter WHERE id = 1 FOR UPDATE');
if($rs == false || mysqli_errno($conn)) {
// 回滚事务
mysqli_query($conn, 'ROLLBACK');
// 重新执行本次操作
$i--;
continue;
}
mysqli_free_result($rs);
$row = mysqli_fetch_array($rs);
$num = $row[0];
mysqli_query($conn, 'UPDATE counter SET num = '.$num.' + 1 WHERE id = 1');
if(mysqli_errno($conn)) {
mysqli_query($conn, 'ROLLBACK');
} else {
mysqli_query($conn, 'COMMIT');
}
}
mysqli_close($conn);
}
for ($i = 0; $i < 10; $i++) {
$pid = pcntl_fork();
if($pid == -1) {
die('can not fork.');
} elseif (!$pid) {
dummy_business();
echo 'quit'.$i.PHP_EOL;
break;
}
}
?>

可以看到，这次业务以期望的方式正确执行了：

mysql> select * from counter;
+----+--------+
| id | num  |
+----+--------+
|  1 | 100000 |
+----+--------+
1 row in set (0.00 sec)

由于悲观锁在开始读取时即开始锁定，因此在并发访问较大的情况下性能会变差。对MySQL Inodb来说，通过指定明确主键方式查找数据会单行锁定，而查询范围操作或者非主键操作将会锁表。接下来，我们看一下如何使用乐观锁解决这个问题，首先我们为counter表增加一列字段：

mysql> select * from counter;
+----+------+---------+
| id | num | version |
+----+------+---------+
| 1 | 1000 | 1000 |
+----+------+---------+
1 row in set (0.01 sec)

实现方式如下：

function dummy_business() {
$conn = mysqli_connect('127.0.0.1', 'public', 'public') or die(mysqli_error());
mysqli_select_db($conn, 'test');
for ($i = 0; $i < 10000; $i++) {
mysqli_query($conn, 'BEGIN');
$rs = mysqli_query($conn, 'SELECT num, version FROM counter WHERE id = 1');
mysqli_free_result($rs);
$row = mysqli_fetch_array($rs);
$num = $row[0];
$version = $row[1];
mysqli_query($conn, 'UPDATE counter SET num = '.$num.' + 1, version = version + 1 WHERE id = 1 AND version = '.$version);
$affectRow = mysqli_affected_rows($conn);
if($affectRow == 0 || mysqli_errno($conn)) {
// 回滚事务重新提交
mysqli_query($conn, 'ROLLBACK');
$i--;
continue;
} else {
mysqli_query($conn, 'COMMIT');
}
}
mysqli_close($conn);
}
for ($i = 0; $i < 10; $i++) {
$pid = pcntl_fork();
if($pid == -1) {
die('can not fork.');
} elseif (!$pid) {
dummy_business();
echo 'quit'.$i.PHP_EOL;
break;
}
}
?>

这次，我们也得到了期望的结果：

mysql> select * from counter;
+----+--------+---------+
| id | num | version |
+----+--------+---------+
| 1 | 100000 | 100000 |
+----+--------+---------+
1 row in set (0.01 sec)

Hibernate框架中同样提供了对两种锁的支持，在此不再赘述了。在高性能系统中处理并发问题，受限于后端数据库，无论何种方式加锁性能都无法高效处理如电商秒杀抢购量级的业务。使用NoSQL数据库、消息队列等方式才能更有效地完成业务的处理。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。