Best Practice to Atomic Read and Write a Field In Database

Question

I am from Java Desktop Application background. May I know what is the best practice in J2EE, to atomic read and write a field in database. Currently, here is what I did

// In Servlet.
synchronized(private_static_final_object)
{
    int counter = read_counter_from_database();
    counter = some_calculation_that_shall_be_done_outside_database(counter);
    write_counter_back_to_database(counter);
}

However, I suspect the above method will work all the time.

As my observation is that, if I have several web request at the same time, I am executing code within single instance of servlet, using different thread. The above method shall work, as different thread web request, are all referring to same "private_static_final_object"

However, my guess is "single instance of servlet" is not guarantee. As after some time span, the previous instance of servlet may destroy, with another new instance of servlet being created.

I also came across http://code.google.com/appengine/docs/java/datastore/transactions.html in JDO. I am not sure whether they are going to solve the problem.

// In Servlet.
Transaction tx = pm.currentTransaction();
tx.begin();
    int counter = read_counter_from_database();  // Line 1
    counter = some_calculation_that_shall_be_done_outside_database(counter);// Line 2                 
    write_counter_back_to_database(counter);     // Line 3
tx.commit();

Is the code guarantee only when Thread A finish execute Line 1 till Line 3 atomically, only Thread B can continue to execute Line 1 till Line 3 atomically?

As I do not wish the following situation happen.

1. Thread A read counter from Database as 0
2. Thread A perform calculation on counter 0
3. Thread B read counter from Database as 0
4. Thread A write calculation result of counter 0 (Say the result is 42) to database
5. Thread B perform calculation on counter 0
6. Thread B write calculation result of counter 0 (Say the result is 42) to database

What I wish is

1. Thread A read counter from Database as 0
2. Thread A perform calculation on counter 0
3. Thread A write calculation result of counter 0 (Say the result is 42) to database
4. Thread B read counter from Database as 42
5. Thread B perform calculation on counter 42
6. Thread B write calculation result of counter 42 (Say the result is 55) to database

Thanks you.

Answer 1

Why not simply combine your two ideas like this:

synchronized(private_static_final_object)
{
    Transaction tx = pm.currentTransaction();
    tx.begin();
        int counter = read_counter_from_database();  
        counter++;                                   
        write_counter_back_to_database(counter);    
    tx.commit();
}

You get your thread synchronization with a critical section in code and atomicity in the db transaction.

Using the synchronized keyword alone will not "protect" db transactional atomicity.

Another option:

The safest thing for you to do, though still not perfect as per rexem's observation, would be to do this within a transaction in a stored procedure within the db engine. Pulling the data, incrementing it and then saving it back opens too many possibilities for erroneous data.

Answer 2

This:

Transaction tx = pm.currentTransaction();
tx.begin();

int counter = read_counter_from_database();  // Line 1
counter++;                                   // Line 2
write_counter_back_to_database(counter);     // Line 3

tx.commit();

..is not safe.

Databases employ what are called isolation levels, where data can be read while an INSERT/UPDATE is being committed. It makes reading faster, but at the risk of getting outdated data. While your counter variable is incremented, I could have committed my insert - you risk a primary or unique key validation error at best, bad data at worst.

My advice is to let the respective database utility handle these situations because they are safe. For Oracle, it's a sequence. SQL Server calls it IDENTITY; MySQL calls it autoincrement...

Answer 3

Not sure if this is acceptable for your usecase but the following is possible all in the database level:

1. begin transaction
2. update counter = counter+1
3. read value
4. commit

When a thread has executed 2 the other threads will block before 2 until the 1st thread does the commit.

Edit: If the update cannot happen on the database then you have to use a select for update style of statement to lock others from executing:

1. begin transaction
2. select counter for update
3. calculate new counter value in application layer
4. update counter on database
5. commit

Now threads will always block on step 2 when another thread is anywhere between 2 and 5. The whole operation will be atomic. Select for update syntax for MySQL InnoDB can be found at: http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html