transaction (TRX)
A transaction is a group of logically related database operations such as SQL statements. When a transaction is executed, compliance with the four basic properties of atomicity, consistency, isolation and durability, also known as ACID paradigms, is guaranteed. To ensure the consistency of data in databases, most systems use transaction management, which also compiles a history from multiple transactions.
Transactions are mostly used in connection with databases and define a sequence of operations (instructions), which represent a logical unit. If due to an error - communication error, access violation or similar - one of the operations cannot be executed, the transaction is first aborted, none of the operations is executed and the database is returned to its initial state, which is called rollback.
When executing a transaction - which may consist of one or more operations - a database system guarantees that the four basic ACID properties are observed:
Atomicity. A transaction is either fully executed or not executed at all. All previous actions are rolled back if any error occurs during the transaction.
Consistency. The database is in a consistent state when the transaction is complete. Logical consistency is ensured by the defined integrity conditions ( key properties, value ranges, foreign keys, etc.). If the integrity conditions are violated, this inevitably leads to a reset of the transaction. Inconsistent states occurring during an execution are corrected again with further operations. This is also referred to as "delayed integrity conditions", which are usually onlychecked at the end of a transaction. An example of this is the operations involved in transferring an account
Isolation. Multiple transactions running at the same time do not affect each other because transactions run in isolation. Since this is fundamental to the consistency of a database, a database management system coordinates mechanisms to synchronize transactions in this regard.
Durability. This is also known as persistence, and means that a successful transaction is permanent. In turn, appropriate management ensures that the effects of a transaction in the dataset remain in perpetuity.
The parallelism of transactions is differentiated in so-called "degrees", and controlled by the Transaction Isolation Level. The higher the level, the fewer problems can occur with regard to the consistency of data. However, it should also be noted that the higher the level of isolation selected, the lower the throughput for a large number of simultaneous accesses. This results from the temporary locking of the data set. As an example, the following is the SQL2 standard, which defines four Transaction Isolation Levels:
Level 0 - READ UNCOMMITTED: No record locking is implemented by the transaction in question. By means of another transaction data can be accessed, which are written by another transaction in parallel. New and obsolete data can be read - this is also called dirty read.
Level 1 - READ COMMITTED: Data records modified by a transaction are locked when written, but remain generally available when read. This can cause the problem of inconsistent reads.
Level2 - REPEATABLE READ: All records read or written by a transaction are locked.
Level 3 - SERIALIZE: Here the complete serializability of the corresponding data records is given. Thus, data read in a transaction is valid until the end of a trans-action.
Example SQL transaction:
SET TRANSACTION READ WRITE WAIT;
UPDATE lager_1 SET price = price * 0.65 WHERE pcs > 125;
UPDATE warehouse_2 SET price = price * 1.2;
COMMIT;
However, not all transaction levels are equally supported by every database system. The order of operations within a transaction must not be changed, because they are ordered and support concurrency of operations.
If transactions are split into different parts and executed in distributed systems, one speaks of distributed transactions. For this purpose, appropriate COMMIT protocols are used to guarantee the atomicity of distributed transactions. If the effects of a transaction are taken back again, this function is called rollback. Provided that a transaction is not feasible due to another transaction, this is called a blocking. And insofar as transactions eliminate each other, this is even called adeadlock. Transactions are marked as having no effect if they have no influence on the data stock.