多线程数据库插入死锁

[xihua1244914259]

package com.test;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.locks.Lock;
public class ThreadRead{

/**Java多线程读大文件
* @param args
*/
public static void main(String[] args) {
int start=(int)System.currentTimeMillis();
System.out.println(start+"");
Thread t1=new Thread(new MultiThreadDemo(),"A");
Thread t2=new Thread(new MultiThreadDemo(),"B");
t1.start();
t2.start();
int end=(int)System.currentTimeMillis();
System.out.println(end+"");
System.out.println((end-start)+" ");
}

}


class MultiThreadDemo implements Runnable{
private static BufferedReader br = null;
private List<String> list;

static{
try {
br = new BufferedReader(new FileReader("E:\\lineitem.tbl"),1024);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void display(List<String> list) {
int count = 0;
System.out.println(list.size());
for(String str:list) {
String[] arrtest = str.split("\\|");
count++;
String sql="insert into newtable (TableName,TupleNum,PropertyName,ElementValue) values (?,?,?,?)";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = testThread.getConnection();
conn.setAutoCommit(false);
pstmt = testThread.getPstmt(conn, sql);
String recordName[]={"L_ORDERKEY","L_PARTKEY","L_SUPPKEY","L_LINENUMBER","L_QUANTITY",
"L_EXTENDEDPRICE","L_DISCOUNT","L_TAX","L_RETURNFLAG","L_LINESTATUS","L_SHIPDATE"
,"L_COMMITDATE", "L_RECEIPTDATE","L_SHIPINSTRUCT","L_SHIPMODE","L_COMMENT"};
for(int i=0;i<arrtest.length;i++){
pstmt.setString(1, "LINEITEM");
pstmt.setInt(2, count);
pstmt.setString(3, recordName[i]);
pstmt.setString(4, arrtest[i]);
pstmt.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
testThread.rollback(conn);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
testThread.close(pstmt);
testThread.close(conn);
}
// System.out.println(str);
}
}
@Override
public void run() {
// TODO Auto-generated method stub
String line = null;
int count=0;
while(true) {
this.list = new ArrayList<String>();
synchronized(br) {
try {
while((line = br.readLine()) != null) {
if(count<15) {
list.add(line);
count++;
}else {
list.add(line);
count = 0;
break;
}
}

} catch (IOException e) {
e.printStackTrace();
}
}
try {
Thread.sleep(1);
display(this.list);
} catch (Exception e) {
e.printStackTrace();
}
if(line == null)
break;
}
}
}

[唐诗三百首]

建议用SQL Profiler工具跟踪一下后台执行的SQL语句和死锁信息,以便分析.

[xihua1244914259]

那是对于sql server的吧。

[xihua1244914259]

多线程进行数据库插入操作，如何避免冲突？

[Tiger_Zhao]

Java 的多线程不熟。
不过看了一下 testThread 应该是 MultiThreadDemo 外的，那么 testThread.getConnection() 这么做到多个连接？
按照命名原理 get 而不是 create 返回的是同一个实例吧。这个多线程安全吗？

[Q315054403]

对SQL SERVER来说，你前端是多线程还是多台电脑发送请求，完全没区别 这是设计问题，比如插入与查询的互相交叉

[xihua1244914259]

谢谢你的回答，基本已解决。