Re: deadlock issue when using mssql2016

[modelrepository <mo...@126.com>]

I can reproduce this deadlock issue with simple and quick steps. That is, say
bundle table has one row, and then start two thread, one thread to delete
it, and another to load it.
In cluster environment, it's possible that the quicker node is trying to
delete one existing node, while another node is just trying to load it
within the sync process. 
One workable fix is re-define the bundle table, that is define primary key
one node_id, rather than defining unique index one that column.  Don't know
why the table schema was defined only with unique index.


public class MSSQLDeadlockTest {
final static String URL =
"jdbc:sqlserver://10.111.3.152:1433;DatabaseName=lcheng";
final static String USER = "sa";
final static String PASSWORD = "Asdf!234";
public static void main(String[] args) throws Exception {
new MSSQLDeadlockTest().deleteBundle();
Connection conn4Delete = DriverManager.getConnection(URL, USER,	PASSWORD);
Connection conn4Get = DriverManager.getConnection(URL, USER, PASSWORD);
for (int i = 0; i < 10000; i++)
{ new MSSQLDeadlockTest().insertBundle(); CountDownLatch start = new
CountDownLatch(1); CountDownLatch end = new CountDownLatch(2); new
Thread(new DeleteRunner(start, end, conn4Delete)).start(); new Thread(new
GetRunner(start, end, conn4Get)).start(); start.countDown(); end.await();
System.out.println("loop " + i + " is completed"); }
}
static class DeleteRunner implements Runnable {
private CountDownLatch start_;
private CountDownLatch end_;
private Connection conn_;
public DeleteRunner(CountDownLatch start, CountDownLatch end,
Connection conn)
{ this.start_ = start; this.end_ = end; this.conn_ = conn; }

@Override
public void run() {
try { start_.await(); } catch (InterruptedException e1) {
e1.printStackTrace(); }

NodeId nodeId = NodeId.valueOf("09fa9c9b-ef26-4a26-ba21-a883ab908e29");
PreparedStatement stmt = null;
ResultSet rs = null;
try { stmt = conn_.prepareStatement("delete from vtjp_def_bundle2 where
NODE_ID = ?"); stmt.setObject(1, nodeId.getRawBytes());
stmt.executeUpdate(); end_.countDown(); } catch (SQLException e) {
e.printStackTrace(); } finally {
if (rs != null) {
try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
}

if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
}
}
}
}

static class GetRunner implements Runnable {
private CountDownLatch start_;
private CountDownLatch end_;
private Connection conn_;

public GetRunner(CountDownLatch start, CountDownLatch end,
Connection conn) { this.start_ = start; this.end_ = end; this.conn_ = conn;
}
@Override
public void run() {
try
{ start_.await(); }
catch (InterruptedException e1)
{ e1.printStackTrace(); }
NodeId nodeId = NodeId.valueOf("09fa9c9b-ef26-4a26-ba21-a883ab908e29");
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn_.prepareStatement("select BUNDLE_DATA from vtjp_def_bundle2
where NODE_ID = ?");
stmt.setObject(1, nodeId.getRawBytes());
rs = stmt.executeQuery();
if (rs.next())
{ byte[] content = (byte[]) rs.getObject(1); System.out.println(new
String(content)); }
else
{ System.out.println("no node"); }
end_.countDown();
} catch (SQLException e)
{ e.printStackTrace(); }
finally {
if (rs != null) {
try
{ rs.close(); }
catch (SQLException e)
{ e.printStackTrace(); }
}

if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
}
}
}
}
public void deleteBundle() throws Exception {
NodeId nodeId = NodeId.valueOf("09fa9c9b-ef26-4a26-ba21-a883ab908e29");
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try
{ conn = DriverManager.getConnection(URL, USER, PASSWORD); stmt =
conn.prepareStatement("delete from vtjp_def_bundle2 where NODE_ID = ?");
stmt.setObject(1, nodeId.getRawBytes()); stmt.executeUpdate(); }
finally {
if (rs != null)
{ rs.close(); }

if (stmt != null) { stmt.close(); }

if (conn != null) { conn.close(); }
}
}


public void insertBundle() throws Exception {
NodeId nodeId = NodeId.valueOf("09fa9c9b-ef26-4a26-ba21-a883ab908e29");
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try { conn = DriverManager.getConnection(URL, USER, PASSWORD); stmt =
conn.prepareStatement("insert into vtjp_def_bundle2 (NODE_ID, BUNDLE_DATA)
values(?, ?)"); stmt.setObject(1, nodeId.getRawBytes()); String content =
"hello world"; stmt.setObject(2, content.getBytes()); stmt.executeUpdate();
} finally {
if (rs != null) { rs.close(); }
if (stmt != null)
{ stmt.close(); }
if (conn != null)
{ conn.close(); }
}
}
}




--
Sent from: http://jackrabbit.510166.n4.nabble.com/Jackrabbit-Users-f510167.html