Transactions specify an isolation level that defines how one transaction is isolated from other transactions. Isolation is the separation of resource or data modifications made by different transactions. Isolation levels are described for which concurrency side effects are allowed, such as dirty reads or phantom reads.
Transaction isolation levels control the following effects:
Whether locks are taken when data is read, and what type of locks are requested.
How long the read locks are held.
Whether read operations referencing rows modified by another transaction:
Block until the exclusive lock on the row is freed.
Retrieve the committed version of the row that existed at the time the statement or transaction started.
Read the uncommitted data modification.
Choosing an isolation level
Choosing a transaction isolation level doesn't affect the locks that are acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies. It holds that lock until the transaction completes, whatever the isolation level set for that transaction. For read operations, transaction isolation levels mainly define how the operation is protected from the effects of other transactions.
A lower isolation level increases the ability of many users to access data at the same time. But it increases the number of concurrency effects, such as dirty reads or lost updates, that users might see. Conversely, a higher isolation level reduces the types of concurrency effects that users might see. But it requires more system resources and increases the chances that one transaction will block another. Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level.
The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation. But it uses a level of locking that is likely to impact other users in multi-user systems. The lowest isolation level, read uncommitted, can retrieve data that has been modified but not committed by other transactions. All concurrency side effects can happen in read uncommitted, however there's no read locking or versioning, so overhead is minimized.
Remarks
The following table shows the concurrency side effects allowed by the different isolation levels.
Isolation Level
Dirty Read
Non-Repeatable Read
Phantom
Read uncommitted
Yes
Yes
Yes
Read committed
No
Yes
Yes
Repeatable read
No
No
Yes
Snapshot
No
No
No
Serializable
No
No
No
Transactions must be run at an isolation level of at least repeatable read to prevent lost updates that can occur when two transactions each retrieve the same row. The transaction then later updates the row based on the originally retrieved values. If the two transactions update rows using a single UPDATE statement and don't base the update on the previously retrieved values, lost updates can't occur at the default isolation level of read committed.
To set the isolation level for a transaction, you can use the setTransactionIsolation method of the SQLServerConnection class. This method accepts an int value as its argument, which is based on one of the connection constants as in the following:
Azure Database for PostgreSQL is a multi-user relational database solution. The ability to support many concurrent users enables PostgreSQL databases to scale out and enable applications that support many users and locations at the same time. The increase in users brings a risk of conflicts. For this reason, it's important to understand the concurrency systems that are in place in Azure Database for PostgreSQL to manage concurrency and conflicts. In this module, we look at both isolation levels and locking
