ACID (atomicity, consistency, isolation, and durability) is an acronym. The acronym stands for Atomicity, Consistency, Isolation, and Durability. In Computer Science ACID is an important concept for databases. ACID is a set of properties that guarantee that database transactions are processed reliably.
Characteristics in detail
The characteristics of these properties as defined by Andreas Reuter and Theo Härder:
Atomicity
The phrase “all or nothing” succinctly describes the of atomicity. The entire transaction fails, and the database state is left unchanged if one part of the transaction fails. If for any reason an error occurs and the transaction is unable to complete all of its steps, then the system is returned to the state it was in before the transaction was started.
Consistency
Consistency is the property that ensures that any changes to values in an instance are consistent with changes to other values in the same instance. If the transaction completes successfully, then all changes to the system will have been properly made, and the system will be in a valid state. If any error occurs in a transaction, then any changes already made will be automatically rolled back.
Isolation
The isolation portion of the ACID Properties is needed when there are concurrent transactions. A transaction in process and not yet committed must remain isolated from any other transaction. If a transaction was not running in isolation, it could access data from the system that may not be consistent. By providing transaction isolation, this is prevented from happening. The Isolation-Level will be presented in the next chapter.
Durability
A transaction is durable in that once it has been successfully completed (a transaction has been committed), all of the changes it made to the system are permanent, even in the case of system failure.
Isolation-Level
The ANSI/ISO SQL standard defines four levels of transaction isolation, with different possible outcomes for the same transaction scenario. In db-systems, isolation determines how transaction integrity is visible to other users and systems. These isolation-levels are defined in terms of phenomena that are either permitted or not at a given isolation level:
Lost Updates
Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.
Example
| order | program 1 5 tickets cancel |
Saved numbers | program 2 3 tickets sell |
|---|---|---|---|
| 1 | 10 | ||
| 2 | Read Number of tickets result: 10 | 10 | |
| 3 | 10 | Read Number of tickets result: 10 | |
| 4 | 5 tickets canceling compute new Number : 10-5=5 Write new number (5) |
5 | |
| 5 | 13 | 3 tickets sell compute new Number : 10+3=13 Write new number (13) |
Dirty Read
A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.
Example
Transaction A begins.
UPDATE ACCOUNT SET DEPOSIT = 2222 WHERE ACCOUNT_ID= ‘4711’;
Transaction B begins.
SELECT * FROM ACCOUNT ;
(Transaction B sees data which is updated by transaction A. But, those updates have not yet been committed.)
Non-Repeatable Read
Non Repeatable Reads happen when in a same transaction same query yields to a different result. This occurs when one transaction repeatedly retrieves the data, while a difference transactions alters the underlying data. This causes the different or non-repeatable results to be read by the first transaction.
Example
This transaction could be used to create a simple user statistics:
Begin TransactionSelect all user names with fewer than five tickets available (action 1a)
Display the List of customers
Select all customers with fewer than five tickets (action 1b)
Display details of these customers
End of Transaction
Simultaneously, the following transaction could be part of the purchase of a new ticket running operations
Begin TransactionAdd new article (action 2a)
Increased number of available tickets of the customer (action 2b)
End of Transaction
| order | transaction 1 | transaction 2 | outcome |
|---|---|---|---|
| 1 | Activity 1a | Activity 2a | Customer will be displayed: Counter = 4 |
| 2 | Activity 2b | Counter will be increase to 5 | |
| 3 | Activity 1b | Customer will not be displayed: Counter less than 5 |
Phantom Read
Phantom read occurs where in a transaction execute same query more than once, and the second transaction result set includes rows that were not visible in the first result set. This is caused by another transaction inserting new rows between the execution of the two queries. This is similar to a non-repeatable read, except that the number of rows is changed either by insertion or by deletion.
The following table provides an overview of the quality and the problems that arise when using the different isolation level
| Isolation level | Lost Updates | Dirty Read | Non-Repeatable Read | Phantom |
|---|---|---|---|---|
| Read Uncommitted | yes | yes | yes | yes |
| Read Committed | no | no | yes | yes |
| Repeatable Read | no | no | no | yes |
| Serializable | no | no | no | no |
Configuration on Wildfly
hibernate.connection.isolation
Sets the JDBC transaction isolation level. Check java.sql.Connection for meaningful values, but note that most databases do not support all isolation levels and some define additional, non-standard isolations.
1: READ UNCOMMITTED
2: READ COMMITTED
4: REPEATABLE READ
8: SERIALIZABLE
<property name=”hibernate.connection.isolation” value=“1″/>
Default-Isolation-Level der Oracle Database ist READ COMMITTED