java mysql死锁_java – 在多线程Spring应用程序中避免MySQL死锁

场景很简单.

我有一个包含两个表的大型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 | ...

...

>在table1上,只有id是主键.该表包含大约12M条目.

>在table2上,id_src和id_trg都是主键,并且都对table1的id具有外键约束,并且它们还启用了DELETE ON CASCADE选项.该表包含大约110M条目.

好吧,现在我正在做的只是创建一个我要从表1中删除的ID列表,然后我正在执行一个简单的DELETE FROM table1 WHERE id IN(< ids>列表);

您可能已经猜到后一个过程也会从table2中删除相应的id.到目前为止一切都很好,但问题是,当我在多线程环境中运行它时,我会遇到很多死锁！

几点说明：

>没有其他进程同时运行也不会(暂时)

>我希望这个快！我有大约24个线程(如果这确实在答案中有任何区别)

>我已经尝试了几乎所有的事务隔离级别(TRANSACTION_NONE除外)Java sql connection transaction isolation

>订购/排序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 can’t specify target table for update in FROM clause

>要从DB中删除的列表,总共可能包含几百万个条目,分为200个块

> FOR UPDATE子句是我听说它锁定了一行而不是锁定整个表

>该应用程序使用Spring的batchUpdate(String sqlQuery)方法,因此会自动管理事务

>所有ID都启用了索引,并且id最多为50个字符！

>在id_src上删除CASCADE和id_trg(每个单独)将意味着table1 id = x上的每个删除都会导致table2上的删除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,在DB级别设置的事务级别是什么,my.cnf中的Mysql配置设置是什么等等…

标签：java,mysql,multithreading,spring

来源： https://codeday.me/bug/20190528/1170093.html