Concurrent Database transactions

Question

My requirement is to fetch a unique ID from the database for processing on the application server(clustered application).For this , I have a table with single cell, holding a value X. This value will be incremented everytime it is fetched.

BEGIN TRAN
      UPDATE ID_TABLE SET VALUE = VALUE + 1
      SELECT VALUE FROM ID_TABLE
END TRAN

Consider a schedule of two transcations(T1 & T2) occurring as follows. UPDATE(T1) , UPDATE(T2) , READ(T1), READ(T2)

Is such interleaving possible , for database transactions.

Are there any ways to avoid this apart from acquiring a lock on the table, in the beginning, and releasing it in the end, which will isolate the transaction?

Answer 1

Is such interleaving possible , for database transactions?

No, at the usual transaction isolation levels, this will not happen (see below).

Are there any ways to avoid this apart from acquiring a lock on the table

Again, in the usual implementations (such as Oracle's) you are already locking the row when you update it. The second transaction will block on its update call until the first one committed. Also, you will not have dirty reads (reading an uncommitted value from the other transaction).

If your database provides a sequence generator, you should probably use that facility (it was made for exactly that purpose).

Answer 2

This is handled by the 'Transaction Isolation level', that you specify.

I don't know how this is done in other DB's, but in Sql Server (and in Access), you can specify the isolation level that should be used when a transaction is started.

The options are:

• read uncommitted
• read committed
• repeatable read
• serializable

For SqlServer, the default isolation level is ReadCommitted. This isolation mode makes sure that other connections cannot read / see the modified data by any other uncommitted transactions.

For more info regarding the Isolation levels, check this article