CODEWITHSUNDEEP
javapostgresqljdbcconcurrencytransactions
heapOverflow
heapOverflow

Reputation: 1275

Running SELECT + UPDATE in a transaction has different results than UPDATE alone?

I'm trying to implement some business logic with JDBC but I don't understand the result I'm getting.

I have n threads each one executing the following pseudocode:

//before threads execution
m <- exec("select x from t where id = 1")

// inside thread
conn.setAutocommit(false);
//do some stuff on the db
v <- exec("select x from t where id = 1")
exec("update t set x = {v}+1 where id=1")
conn.commit();

// after threads execution
exec("select x from t where id = 1") //result should be m+n, but it isn't

When I run this code the x column of the t table is not increased by n as I expect. There is of course a problem of concurrency among all the threads.

If I substitute that code with the following code, then it works properly:

//before threads execution
m <- exec("select x from t where id = 1")

// inside thread
conn.setAutocommit(false);
//do some stuff on the db
exec("update t set x = x+1 where id=1")
conn.commit();

// after thread execution
exec("select x from t where id = 1") //result is be m+n

But since the first code is inside a transaction, shouldn't be equivalent to the second version?

I also tried to restrict the isolation level to SERIALIZABLE but I observe always the same behavior.

EDIT: I rewrote my code with a different connection per Thread and the result is the same.

Upvotes: 0

Views: 1303

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324485

But since the first code is inside a transaction

No. Transactions are not magic that lets you ignore concurrency. They make specific, well defined promises about when data is visible, about locking, etc, but they don't make concurrency go away. See PostgreSQL's documentation on concurrency and isolation.

There can even be concurrency effects in single-statement operations, so reducing things to a single (complex) statement won't make concurrency go away.

In this case SERIALIZABLE isolation will help you:

CREATE TABLE sdemo
(
    id integer primary key,
    counter integer
);

INSERT INTO sdemo VALUES (1, 1);

then

SESSION1                      SESSION2
BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
                              BEGIN TRANSACTION 
                              ISOLATION LEVEL SERIALIZABLE;
SELECT counter FROM sdemo
WHERE id = 1;
-- result is '1'
                              SELECT counter FROM sdemo
                              WHERE id = 1;
                              -- result is '1'                        

UPDATE sdemo
SET counter = 2
WHERE id = 1;
-- Succeeds

                              UPDATE sdemo
                              SET counter = 2
                              WHERE id = 1;
                              -- hangs waiting on row lock
                              -- held by session 1


COMMIT;
-- Succeeds
                              -- UPDATE finishes (succeeds)

                              COMMIT;
                              -- Aborts with
                              ERROR: could not serialize access due 
                                     to concurrent update

because PostgreSQL detects that one xact changed the row while another read it, then the other xact tried to change it.

However, it'd be much simpler to select x from t where id = 1 FOR UPDATE in your first example. This takes a row lock that means no other transaction can modify the row before yours commits or rolls back.

Upvotes: 2

Related Questions