Hello all!

I have a test case with multiple writers all trying to update the same row, which throws "update conflicts with concurrent update," below.

That is expected to me, since Firebird throws an error when a we attempt to update the record that another thread has updated underneath it.

This always get users coming from MySQL, though, since MySQL InnoDB has a "last one wins" belief and no error is thrown.

This is with FB 2.1.1 and jaybird 2.1.3 on win32.

Two questions...

#1 - Is there some way to force this test to run successfully, for illustration, with no error messages? I had thought that some combination of using "WITH LOCK" and transaction isolation other than RC would do the trick, but I can't seem to get it to work.

In that case the writes would be serialized, basically limiting the system to one-at-a time writes.




#2 - What is best practice for coding this up in a web form, like a JSP page? For example, if you have 2 customer service reps who change the street address of a customer at the same time, I assume that the catch block of the code should tell the user that the update failed, and prompt them to try again?


Here is my test data

Database: concurrent.fdb

SQL> create table employee (emp_id integer, emp_name char(20) );
SQL> insert into employee values (1, 'bob');
SQL> insert into employee values (2, 'joe');
SQL> insert into employee values (3, 'lisa');
SQL> insert into employee values (4, 'fred');
SQL> insert into employee values (5, 'sam');
SQL> commit;
SQL> quit;






package com.sas.bioliv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ConcurrentUpdates {

public static void main(String[] args) throws Exception {
(new ConcurrentUpdates()).execute();
System.out.println("ConcurrentUpdates.main() executed successfully");
}

private void execute() throws Exception {
int threadCount = 20;
Thread[] threads = new Thread[threadCount];
for (int i = 0; i < threadCount; i++) {
Connection connection = getConnection();
Thread thread = createThread(i, connection);
threads[i] = thread;
}
for (int i = 0; i < threads.length; i++) {
threads[i].start();

}

}

private Thread createThread(final int threadNumber,
final Connection connection) {
Thread thread = new Thread(threadNumber + "") {
// @Override
public void run() {
try {
updateEmployeeName(connection, 1, "B");
// updateEmployeeName(connection,threadNumber,"B");
} catch (SQLException sqle) {
System.out.println ("sqlstate: " + sqle.getSQLState());
sqle.printStackTrace();
} finally {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}

} catch (SQLException sqle) {
sqle.printStackTrace();
}
}

}

};
return thread;

}

private void updateEmployeeName(Connection connection, int id,
String employeeName) throws SQLException {
connection
.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
connection.setAutoCommit(false);
PreparedStatement ps1 = connection
.prepareStatement("select * from employee where emp_id = ? with lock");
ps1.setInt(1, id);
ps1.execute();
PreparedStatement ps2 = connection
.prepareStatement("update employee set emp_name = ? where emp_id = ?");
ps2.setString(1, employeeName);
ps2.setInt(2, id);
ps2.executeUpdate();
connection.commit();
System.out.println("updated!");

// connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
// connection.setAutoCommit(false);
// PreparedStatement ps2 = connection
// .prepareStatement("update employee set emp_name = ? where emp_id =
// ?");
// ps2.setString(1, employeeName);
// ps2.setInt(2, id);
// ps2.executeUpdate();
// connection.commit();

}

private Connection getConnection() throws Exception {
Class.forName ("org.firebirdsql.jdbc.FBDriver");
String url = "jdbc:firebirdsql:/fbdata/concurrent.fdb";
return DriverManager.getConnection(url, "sysdba", "masterkey");

}

}