Concurrency / Synchronization question

Question

I have 2 programs running on 2 different machines.
Each program has a method called updateRecord that does the following 2 things:
1. Do a SELECT query on a particular record Z
2. Do a UPDATE query on the same record.

If these 2 queries are in the same transaction (between beginTransaction and commitTransaction) does it guarantee proper execution?

i.e, will the following sequence of operations fail to execute successfully?

1. Prog-1 SELECT
2. Prog-2 SELECT
3. Prog-1 UPDATE
4. Prog-2 UPDATE

OR

1. Prog-1 SELECT
2. Prog-1 UPDATE
3. Prog-1 SELECT
4. Prog-2 UPDATE
5. Prog-1 COMMIT
6. Prog-2 COMMIT

Answer 1

The two machines will never use the same transaction. If the SELECT & UPDATE are performed within a stored procedure, they will be within the same transaction. If the SELECT & UPDATE queries are run as separate statements, then the following is a possibility:

1. Prog-1 SELECT
2. Prog-2 SELECT
3. Prog-1 UPDATE
4. Prog-2 UPDATE

Depending on the database isolation level, machine #2's select could be looking at data before the UPDATE for machine #1 is run. IIRC, this would be the case by default on Oracle.

Here's the 411 on Oracle's Isolation Levels, per AskTom.

For MySQL, use the SET TRANSACTION command. For more info on MySQL's isolation level support, see this link.

Answer 2

Your program needs to lock the record when it selects it - e.g. use the SELECT FOR UPDATE syntax. That way, the record will be locked until the UPDATE is done.

Answer 3

As already noted, using SELECT ... FOR UPDATE helps as it locks the row until the transaction is committed(or rolled back).

You don't need two machines to test this. By using two different sessions(for instance by running two different SQL*Plus instances) and running your queries concurrently in a specific order on both sessions, you will be able to reproduce the concurrency issue(s) if there are any.

In this case you could run:

Session1: SELECT z AS sel_z -- sel_z = 0
Session1: UPDATE z = sel_z + 1
Session2: SELECT z AS sel_z -- (1) sel_z = 0 because Session1 is uncommitted
Session2: UPDATE z = sel_z + 1
Session1: COMMIT
Session2: COMMIT
Session1: SELECT z AS sel_z -- sel_z = 1
Session2: SELECT z AS sel_z -- sel_z = 1

The problem is at (1), Session2 doesn't see the values changed by Session1 because they are not committed.

My suggestion is not to think in terms of changing the TX isolation level, think about locking the proper resources.