CODEWITHSUNDEEP
javamysql
user291701
user291701

Reputation: 39681

Java sql read+write - prevent race condition?

I have two threads calling a method that performs a read and then a write of a record in a mysql database. Some pseudo code:

public void incrementRowValue() {
    Connection conn = ...;
    try {
        conn.setAutoCommit(false);

        int value = conn.execute("select value from foo where id = 123");
        value += 1;
        conn.execute("update foo set value = " + value + " where id = 123");  

        conn.commit();
    }
    catch (SQLException ex) {
        conn.rollback(); 
    }
    finally {
        conn.close();
    }
}

So if the threads execute about the same time, it looks like the read+write is not performed as one atomic item. For example, with a starting value of zero, my logs show that if both threads executed at about the same time, the ending value can be one instead of two.

Is there a different locking level I can use to prevent this? I can synchronize in java code if necessary of course, but I'm probably missing a big feature here that the database level provides for us.

Thanks

http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

Upvotes: 0

Views: 1125

Answers (3)

Robin Dijkhof
Robin Dijkhof

Reputation: 19288

This is known as the lost update problem. You could use a Exclusive lock. This will prevent other transactions from reading the locked data.

Upvotes: 1

Raju Rathore
Raju Rathore

Reputation: 149

You can manage database concurrency using isolation levels

Isolation Level     Dirty Read  Nonrepeatable Read  Phantom Read
READ UNCOMMITTED    Permitted   Permitted   Permitted
READ COMMITTED  --  Permitted   Permitted
REPEATABLE READ     --  --  Permitted
SERIALIZABLE

SERIALIZABLE-it provide highest level of concurrency

For details please  refer :

[http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html][1]

Upvotes: 0

Tim B
Tim B

Reputation: 41188

The simplest way to do this is to wrap the SQL into one statement and let the SQL server handle it, I can't remember the exact syntax off hand but it would be something like:

    conn.execute("update foo set value = (select value from foo where id = 123)+1 where id = 123");

The SQL server will lock the row being used (and if using multiple rows will lock them all) and perform the operation atomically.

This will also be faster/more efficient as it only makes the single database call instead of two. This sort of update could really do with being wrapped into a stored procedure though.

Upvotes: 2

Related Questions