多线程中的准备好的语句 [英] prepared statement in multithreading

问题描述

我在准备好的语句中使用了MERGE命令，当我在单线程环境中执行该命令时，它可以正常工作，但是在多线程环境中，这会引起一些问题.即数据是重复的，即如果我有5个线程，每个记录将重复5次.我认为db中没有锁来帮助该线程. 我的代码:

I have used MERGE command in my prepared statement,and when i was executed it in a single threaded env,its working fine,But in multi threaded environment,it causes some problem.That is data is duplicated,that is if i have 5 threads,each record will duplicate 5 times.I think there is no lock in db to help the thread. My code:

//db:oracle
sb.append("MERGE INTO EMP_BONUS EB USING (SELECT 1 FROM DUAL) on (EB.EMP_id = ?) WHEN MATCHED  THEN  UPDATE SET TA =?,DA=?,TOTAL=?,MOTH=?  WHEN NOT MATCHED THEN "+ "INSERT (EMP_ID, TA, DA, TOTAL, MOTH, NAME)VALUES(?,?,?,?,?,?) ");

//sql operation,calling from run() method
public void executeMerge(String threadName) throws Exception {              
        ConnectionPro cPro = new ConnectionPro();
        Connection connE = cPro.getConection();
        connE.setAutoCommit(false);
        System.out.println(sb.toString());
        System.out.println("Threadname="+threadName);
        PreparedStatement   pStmt= connE.prepareStatement(sb.toString());
        try {
            count = count + 1;

            for (Employee employeeObj : employee) {//datalist of employee

                pStmt.setInt(1, employeeObj.getEmp_id());
                pStmt.setDouble(2, employeeObj.getSalary() * .10);
                pStmt.setDouble(3, employeeObj.getSalary() * .05);
                pStmt.setDouble(4, employeeObj.getSalary()
                        + (employeeObj.getSalary() * .05)
                        + (employeeObj.getSalary() * .10));
                pStmt.setInt(5, count);
                pStmt.setDouble(6, employeeObj.getEmp_id());
                pStmt.setDouble(7, employeeObj.getSalary() * .10);
                pStmt.setDouble(8, employeeObj.getSalary() * .05);
                pStmt.setDouble(9, employeeObj.getSalary()
                        + (employeeObj.getSalary() * .05)
                        + (employeeObj.getSalary() * .10));
                pStmt.setInt(10, count);
                pStmt.setString(11, threadName);
                // pStmt.executeUpdate();

                pStmt.addBatch();
            }

            pStmt.executeBatch();
            connE.commit();
        } catch (Exception e) {
            connE.rollback();
            throw e;

        } finally {

            pStmt.close();
            connE.close();

        }
    }

如果employee.size = 5，线程数= 5，执行后我将得到25条记录，而不是5条记录

if employee.size=5, thread count =5,after execution i would get 25 records instead of 5

推荐答案

如果没有约束(即emp_bonus中emp_id列上的主键或唯一键约束)，则没有什么可以防止的数据库允许每个线程插入5行.由于每个数据库会话都看不到其他会话所做的未提交的更改，因此每个线程都将看到emp_bonus中没有与该线程在寻找的emp_id行(我假设employeeObj.getEmp_id()返回相同的5 emp_id值)，因此每个线程将插入所有5行，如果有5个线程，则总共有25行.如果您有防止插入重复行的唯一约束，则Oracle将允许其他4个线程阻塞，直到第一个线程提交，允许后续线程进行更新而不是插入.当然，这将导致线程被序列化，从而使您无法运行多个线程而获得任何性能提升.

If there is no constraint (i.e. a primary key or a unique key constraint on the emp_id column in emp_bonus), there would be nothing to prevent the database from allowing each thread to insert 5 rows. Since each database session cannot see uncommitted changes made by other sessions, each thread would see that there was no row in emp_bonus with the emp_id the thread is looking for (I'm assuming that employeeObj.getEmp_id() returns the same 5 emp_id values in each thread) so each thread would insert all 5 rows leaving you with a total of 25 rows if there are 5 threads. If you have a unique constraint that prevents the duplicate rows from being inserted, Oracle will allow the other 4 threads to block until the first thread commits allowing the subsequent threads to do updates rather than inserts. Of course, this will cause the threads to be serialized defeating any performance gains you would get from running multiple threads.

这篇关于多线程中的准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！