场景很简单。
我有一个有点大的MySQL数据库,其中包含两个表:
-- Table 1
id (primary key) | some other columns without constraints
-----------------+--------------------------------------
1 | foo
2 | bar
3 | foobar
... | ...
-- Table 2
id_src | id_trg | some other columns without constraints
-------+--------+---------------------------------------
1 | 2 | ...
1 | 3 | ...
2 | 1 | ...
2 | 3 | ...
2 | 5 | ...
...
- 在表 1 上,只有
id
是主键。此表包含大约 12M 个条目。 - 在表 2 上,
id_src
和id_trg
都是主键,并且对 table1 的id
都有外键约束,并且它们还启用了选项DELETE ON CASCADE
。此表包含大约 110M 个条目。
好的,现在我所做的只是创建一个要从表 1 中删除的id
列表,然后我正在执行一个简单的DELETE FROM table1 WHERE id IN (<the list of ids>);
后一个过程可能已经猜到了,也会从表2中删除相应的id。到目前为止一切顺利,但问题是当我在多线程 env 上运行它时,我得到了很多Deadlocks
!
一些注意事项:
- 没有其他进程同时运行,也不会(暂时)
- 我希望这快!我有大约 24 个线程(如果这确实对答案有任何影响)
- 我已经尝试了几乎所有的事务隔离级别(除了TRANSACTION_NONE) Java sql 连接事务隔离 排序
- /排序ID我认为无济于事!
我已经尝试过
SELECT ... FOR UPDATE
,但是一个简单的DELETE
最多需要30秒!(所以使用它没有用):DELETE FROM table1 WHERE id IN ( SELECT id FROM ( SELECT * FROM table1 WHERE id='some_id' FOR UPDATE) AS x);
我该如何解决这个问题?
我将不胜感激任何帮助和提前感谢:)
编辑:
- 使用 InnoDB 引擎
- 在单个线程上,此过程需要十几个小时甚至一整天,但我的目标是几个小时!
- 我已经在使用连接池管理器:
java.util.concurrent
- 有关双嵌套
SELECT
的说明,请参阅 MySQL 无法在 FROM 子句中指定要更新的目标表 - 要从数据库中删除的列表总共可能包含数百万个条目,这些条目分为 200 个块
FOR UPDATE
子句是我听说它锁定了一行而不是锁定整个表- 该应用程序使用Spring的batchUpdate(String sqlQuery)方法,因此交易是自动管理的 。
- 所有 id 都启用了索引,并且 id 是唯一的,最多 50 个字符!
-
id_src
和id_trg
(分别)的DELETE ON CASCADE
意味着表1id=x
上的每次删除都会导致表2上的删除id_src=x
和id_trg=x
根据要求编写一些代码:
public void write(List data){ try{ Arraylist idsToDelete = getIdsToDelete(); String query = "DELETE FROM table1 WHERE id IN ("+ idsToDelete + " )"; mysqlJdbcTemplate.getJdbcTemplate().batchUpdate(query); } catch (Exception e) { LOG.error(e); } }
而myJdbcTemplate
只是一个扩展JdbcDaoSupport
的抽象类。
,您传递 id 的第一个简单删除查询,如果您将 id 传递到 1000 这样的限制,应该不会产生问题(子表中的总行数也应该接近大约,但不是很多,比如 10,000 等),但如果你传递的像 50,000 或更多,那么它可能会产生锁定问题。
为避免死锁,您可以按照以下方法来解决此问题(假设批量删除不会成为生产系统的一部分)-
步骤1:通过选择查询获取所有id并保持在光标中。
步骤2:现在逐个删除存储在存储过程中游标中的这些ID。
注意:要检查为什么删除会获取锁,我们必须检查几件事,例如您传递了多少 ID、在数据库级别设置的事务级别是什么、my.cnf 中的 Mysql 配置设置是什么等......
删除许多 (> 10000) 父记录,每个父记录都有级联删除的子记录,这可能是危险的,因为一次删除的记录越多,导致死锁或回滚的锁定冲突的可能性就越大。
如果可以接受(意味着您可以与数据库建立直接的JDBC连接),您应该(此处不涉及线程):
- 计算要删除的 ID 列表 每 100
- 或 1000 条记录分批删除它们(先验在 10 到 100 之间)
由于较重的工作应该在数据库部分,我毫不怀疑线程在这里会有所帮助。如果你想尝试一下,我推荐:
- 单个线程(具有专用数据库连接)计算要删除的 ID 列表并用它们填充同步队列
- 少量线程(4 或 8 个),每个线程都有自己的数据库连接:
- 批量使用准备好的
DELETE FROM table1 WHERE id = ?
- 从队列中获取 ID 并准备批处理
- 每 10 或 100 条记录向数据库发送一批
- 每 10 或 100 批执行一次提交
- 批量使用准备好的
我无法想象整个过程会花费超过几分钟的时间。
经过一些其他的阅读,看起来我已经习惯了旧系统,而且我的数字真的很保守。
好的,这就是我所做的,它实际上可能无法避免死锁,但目前是我唯一的选择。
这个解决方案实际上是使用Spring处理MySQL死锁的一种方式。
捕获并重试死锁:
public void write(List data){
try{
Arraylist idsToDelete = getIdsToDelete();
String query = "DELETE FROM table1 WHERE id IN ("+ idsToDelete + " )";
try {
mysqlJdbcTemplate.getJdbcTemplate().batchUpdate(query);
} catch (org.springframework.dao.DeadlockLoserDataAccessException e) {
LOG.info("Caught DEADLOCK : " + e);
retryDeadlock(query); // Retry them!
}
} catch (Exception e) {
LOG.error(e);
}
}
public void retryDeadlock(final String[] sqlQuery) {
RetryTemplate template = new RetryTemplate();
TimeoutRetryPolicy policy = new TimeoutRetryPolicy();
policy.setTimeout(30000L);
template.setRetryPolicy(policy);
try {
template.execute(new RetryCallback<int[]>() {
public int[] doWithRetry(RetryContext context) {
LOG.info("Retrying DEADLOCK " + context);
return mysqlJdbcTemplate.getJdbcTemplate().batchUpdate(sqlQuery);
}
});
} catch (Exception e1) {
e1.printStackTrace();
}
}
另一种解决方案可能是使用Spring的多步机制。
因此,将 DELETE 查询拆分为 3 个,从而通过删除阻塞列开始第一步,其他步骤分别删除其他两列。
Step1: Delete id_trg from child table;
Step2: Delete id_src from child table;
Step3: Delete id from parent table;
当然,最后两个步骤可以合并为 1,但在这种情况下,需要两个不同的 ItemsWriter!