使用PreparedStatements时SQL Server死锁 [英] SQL Server deadlock when using PreparedStatements

问题描述

我有一个Java Servlet应用程序，并且正在使用准备好的查询来更新SQL Server数据库表中的记录.

让我说我要执行UPDATE MyTable SET name = 'test' WHERE id = '10'. (是的，id是一个varchar)
我使用以下代码实现了这一目的:

PreparedStatement pstmt = con.prepareStatement("UPDATE MyTable SET name = ? WHERE id = ?");
pstmt.setString(1, getName() );
pstmt.setString(2, getID() );
pstmt.executeUpdate();

我发现，当我运行一个JMeter脚本来模拟2个用户时，此语句导致数据库中出现死锁.

我想检查一下SQL事件探查器中的值，因此我使用了以下代码，因此可以检查这些值.

String query = String.format("UPDATE MyTable SET name = '%s' WHERE id = '%s' ", getName(), getID() );
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.executeUpdate();

突然我的僵局消失了！遗憾的是，最后一种方法容易受到SQL注入的攻击.<​​/p>

有没有人可以告诉我正在发生的事情和/或如何修复它?

解决方案

好，我终于找到了问题和解决方案.

jTDS JDBC驱动程序与MSSQL的结合似乎是问题".

本文准确地解释了我的情况.借助此常见问题解答，我能够将数据源设置为正确的配置./p>

据我了解:

如果您有使用类似字符串的索引的语句(就我而言)，该表将执行索引SCAN而不是索引SEEK.这将导致整个表被锁定，并且容易出现死锁.

我希望这也会对其他人有所帮助.

I have a java servlet application and I'm using a prepared query to update a record in a SQL Server Database table.

Lets say I want to execute UPDATE MyTable SET name = 'test' WHERE id = '10'. (Yes, id is a varchar)
I used the following code to make this happen:

PreparedStatement pstmt = con.prepareStatement("UPDATE MyTable SET name = ? WHERE id = ?");
pstmt.setString(1, getName() );
pstmt.setString(2, getID() );
pstmt.executeUpdate();

I found out that while I was running a JMeter script to simulate 2 users, this statement causes a deadlock in my database.

I wanted to check what my values were in the SQL Profiler so I used the following code, so I could check the values.

String query = String.format("UPDATE MyTable SET name = '%s' WHERE id = '%s' ", getName(), getID() );
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.executeUpdate();

Suddenly my deadlock was gone! It's a shame the last approach is vulnerable to SQL injection.

Is there somebody who can tell me what is going on and/or how to fix it?

解决方案

Ok I finally found the problem and solution to my problem.

It seemed that the combination of the jTDS JDBC driver with MSSQL was the 'problem'.

This article explained my situation exactly. And with the help of this FAQ I was able to set the datasource to the right configuration.

From what I understand:

If you have statement that uses a String-like index (Like in my situation), the table performs an index SCAN instead of an index SEEK. This causes the whole table to be locked and vulnerable to deadlocks.

I hope this will help other people too.

这篇关于使用PreparedStatements时SQL Server死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！