Reading query within a database transaction

Question

I was wondering what is the point in performing a read from a database table within a database transaction? In my case, it is one single select statement on 1 table. This table could be updated by other threads. How does a database transaction help me in this situation? I am having trouble finding information on this specific case.

Answer 1

The transaction can help you define the level of separation between your query and concurrent updates.You define how concurrent updates can influence the data you are getting as result. It may be important to ensure data consistency in the result. This site lists the common isolation modes.

• none: No transaction isolation.

• read-committed: Dirty reads are prevented; non-repeatable reads and phantom reads can occur.

• read-uncommitted: Dirty reads, non-repeatable reads and phantom reads can occur.

• repeatable-read: Dirty reads and non-repeatable reads are prevented; phantom reads can occur.

• serializable: Dirty reads, non-repeatable reads, and phantom reads are prevented.

EDIT: A non-repeatable read means that the data you will get from a query takes newly commited data into account, so the result can be different if the query executed multiple times. A dirty read is similar, but you also can read uncommited data from other transactions.

You should only care for isolation if you need consistent data at all times. It might sound strange not to care about consistency, but it is actually often traded for performance in real applications.

For example the 'x in stock' display you see in many web shops is often obtained without complete isolation. If two customers try to buy the last item in stock, one will get the last item and the other will have to wait until the item has been reordered by the shop.

How you determine and define the used isolation depends on the language, database and frameworks you are using. In Hibernate, for example, you can set the isolation property on the Connection:

connection.setTransactionIsolation(Connection.READ_UNCOMMITTED);