从 JAVA 执行操作时，如何最大限度地减少数据库表上的死锁?

Question

我面临异常com.microsoft.sqlserver.jdbc.SQLServerException：事务(进程 ID 493)在锁定 | 与另一个进程通信缓冲区资源时死锁，并被选为死锁受害者。重新运行事务。

当大量用户访问我网站的特定事务时。这是因为表上有锁，而其他人请求获取此特定表的锁。 此外，此特定事务还使用了 20 个表，对于至少五个表，首先执行删除查询，然后插入新数据，这可能会长时间保存表并导致死锁。下面是示例代码。

public void save2(){
con = DBConnFactory.getConnection();
con.setAutoCommit(false);
String deleteQuery1 = "delete from TEST_TABLE1";
String insertQuery1 = "insert into TEST_TABLE1 values ('66','7')";
String deleteQuery2 = "delete from TEST_TABLE2";
String insertQuery2 = "insert into TEST_TABLE2 values ('66','7')";
String deleteQuery3 = "delete from TEST_TABLE3";
String insertQuery3 = "insert into TEST_TABLE3 values ('66','7')";
String deleteQuery4 = "delete from TEST_TABLE4";
String insertQuery4 = "insert into TEST_TABLE4 values ('66','7')";
ps1 = con.prepareStatement(deleteQuery1);
ps2 = con.prepareStatement(insertQuery1);
ps1.executeUpdate();
ps2.executeUpdate();
ps1 = con.prepareStatement(deleteQuery2);
ps2 = con.prepareStatement(insertQuery2);       
ps1.executeUpdate();
ps2.executeUpdate();
ps1 = con.prepareStatement(deleteQuery3);
ps2 = con.prepareStatement(insertQuery3);
ps1.executeUpdate();
ps2.executeUpdate();

ps1 = con.prepareStatement(deleteQuery4);
ps2 = con.prepareStatement(insertQuery4);
ps1.executeUpdate();
ps2.executeUpdate();
System.out.println("success2");
con.commit();

} public class DBConnection {

public static Connection getConnection() {
Connection conn = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource dataSource = (DataSource) envContext.lookup("jdbc/DBConnection");
if ((conn == null) || conn.isClosed()) {
conn = dataSource.getConnection();
}
} catch (NamingException e) {
LOG.error("DBConnFactory - JNDI namin error in getConnection =>"+ e.getMessage());
} catch (SQLException e) {
LOG.error("DBConnFactory - SQL error in getConnection =>"+ e.getMessage());
} 
return conn;
}
}

我正在考虑删除存储过程中的所有表数据，然后根据我的业务逻辑通过 JAVA 插入。会有帮助吗？ 请建议如何解决此问题，我需要改变我的方法吗？

Answer 1

由于您要删除表中的所有行，请尝试改用TRUNCATE TABLE TEST_TABLE1。 Truncate 比 Delete 快得多，但由于它是 DDL 语句，因此对其使用有额外的限制。

String deleteQuery1 = "truncate table TEST_TABLE1;";

您可以使用的另一种方法是将 Delete 和 Insert 语句合并到单个批处理中：

String query1 = "delete from TEST_TABLE1; insert into TEST_TABLE1 values ('66','7');";

不带 where 子句的 Delete 语句采用全表锁，这将阻止执行其他删除或插入语句。 通过将这两个语句合并到一个批处理中，您将减少连接数和 延迟。

您可以将所有删除和插入语句合并到一个批处理中。

一个重要的问题是您使用不带 where 子句的 Delete 功能。 由于您遇到死锁，这意味着您有多个连接同时击中相同的对象。 例如，如果连接 A 正在插入到Test_Table1中，而连接 B 正在尝试从表中删除，则存在软冲突的巨大风险，在软冲突中，连接会在连接 A 中的数据用于其他任何内容之前将其删除。 您确实不应该在这样的事务系统中使用没有 Where 子句的 Delete 子句。

如果您尝试删除要插入的特定值，则应添加 Where 子句来限制它。 这也可能将锁定级别从表更改为行级别(假设您具有适当的索引)，并消除死锁。

String query1 = "delete from TEST_TABLE1 where ID = '66'; insert into TEST_TABLE1 values ('66','7');";