事务型数据库与死锁预防

Question

我正在开发云服务，其中同步本地数据库(订单收取POS系统)到中央远程数据库的过程中。我们的远程数据库运行的是MySQL5.6的最新版本，每个表都有innoDB表引擎。基本上，它作为一个事务性数据库，经历了大量的事务，主要写入(即。不可避免地，我们遇到了第一次数据库死锁(请注意，这发生在升级MySQL5.6之前)，我对死锁原因的理解可能是有两个连接试图同时读取或写入一行。我也知道死锁很常见，需要正确的代码来尝试/捕获死锁，我相信我已经成功完成了这一点。为了减少死锁，我正在考虑设置2个数据库，它们是彼此的镜像A)写入b)读取的数据库

基本上，从本地到远程的同步将写入数据库A，并且在后台使用rsync会将数据库A镜像到B，数据库B将用于读取和查询报告，因为rsync不使用数据库连接来读取同步数据。

我的问题是，这种结构在减轻死锁方面是否有效，并在服务器上存在任何重大的性能问题？

我希望我的问题对我正在努力实现的目标有意义。

提前谢谢。

Answer 1

死锁并不是两个连接试图同时写入同一行的结果。它只是比这稍微复杂一点。

会产生死锁的场景是这样的：

有两个资源(可以是一个表中的两行，两个表，两个文件等，我们将其标识为"A“和"B")。

1. Connection #1锁定资源"A"
2. Connection #2锁定资源"B"
3. Connection #1继续尝试锁定"B“(同时仍将其锁定在”A“上)。因为连接#2已经锁定了"B“，所以连接#1等待锁被released
4. Connection #2 (仍然持有它在”B“上的锁)试图锁定"A”。由于连接#1已锁定"A“，因此连接#2将等待该锁被释放。

两个连接现在都处于一种状态，每个连接都在等待对方释放其锁；它们都是死锁的。只有当一个连接中止释放其锁的操作，允许另一个连接完成时，才能打破死锁。

这种情况可能发生在数据库中的任何粒度级别，从行到页再到表，数据或索引也可能发生这种情况。让数据库引擎自行执行乐观锁定通常可提供最佳性能，但也可能导致死锁。您可能需要在可序列化的事务中执行一些插入/更新(尽管这会带来性能损失)。将涉及多个表的事务视为死锁场景中的潜在参与者。

Answer 2

数据库复制有助于提高性能和可用性。但是，在开始复制数据库之前，您可能需要检查多个方面。

我假设您已经打开了用于记录慢查询的旋钮。我建议分析并查看是否有机会优化导致查询的问题。另一件事是，虽然对数据进行重新建模并不总是那么容易，但它有时会有所帮助。验证您的报表查询是否按需使用了索引。您还可以查看数据分区。

在某些时候，根据您的数据量和报告要求，您可以很好地考虑将NoSQL数据库用于分析数据。

Answer 3

放弃完全消除死锁的想法。专注于“缓解”它们。

只要可行，就以某种规范的顺序触摸表和行。一个简单的例子是对IN子句中的元素进行排序。我认为以下情况可能会导致死锁：

UPDATE ... WHERE id IN (3,7) -- in one connection
UPDATE ... WHERE id IN (7,3) -- in another connection

但是，如果对所有事务中的in进行排序，最糟糕的结果是一个连接将等待另一个释放行锁。(这就是innodb_lock_wait_timeout发挥作用的地方。)

另一个例子包括

BEGIN;
SELECT ... FROM table1  FOR UPDATE;
SELECT ... FROM table2  FOR UPDATE;
...
COMMIT;

当然，在某些情况下，您可能无法预测事务中需要哪些ids或表，因此无法进行一些简单的排序。这就是为什么你必须准备好捕捉死锁。

当您遇到死锁时，只需重放事务即可。好吧，如果你在事务中散布了其他代码，这可能并不简单。