Database transaction

A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. Transactions in a database environment have two main purposes:

To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs’ outcomes are possibly erroneous.
A database transaction, by definition, must be atomic, consistent, isolated and durable.Database practitioners often refer to these properties of database transactions using the acronym ACID.

Transactions provide an “all-or-nothing” proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.

A transactional database is a DBMS where that provides the ACID properties for a bracketed set of database operations (begin-commit). All the write transactions within a transaction have an all-or-nothing effect, that is, either the transaction succeeds and all updates take effect, or otherwise, the database is brought to a state that does not include any of the updates of the transaction. Transactions also take care that the effect of concurrent transactions satisfies certain guarantees known as isolation level. The highest isolation level is serializability that guarantees that the effect of concurrent transactions is equivalent to a serial (i.e. sequential) execution of them.

Most modern relational database management systems fall into the category of databases that support transactions. A new category of data stores known as NoSQL data stores has emerged during the last decade. NoSQL data stores pursue scalability and due to the lack of scalable solutions for transactional processing they renounced to provide transactions and therefore to guarantee data consistency in the advent of updates and concurrent accesses.

In a database system a transaction might consist of one or more data-manipulation statements and queries, each reading and/or writing information in the database. Users of database systems consider consistency and integrity of data as highly important. A simple transaction is usually issued to the database system in a language like SQL wrapped in a transaction, using a pattern similar to the following:

Begin the transaction
Execute a set of data manipulations and/or queries
If no errors occur then commit the transaction and end it
If errors occur then roll back the transaction and end it
If no errors occurred during the execution of the transaction then the system commits the transaction. A transaction commit operation applies all data manipulations within the scope of the transaction and persists the results to the database. If an error occurs during the transaction, or if the user specifies a rollback operation, the data manipulations within the transaction are not persisted to the database. In no case can a partial transaction be committed to the database since that would leave the database in an inconsistent state.

Internally, multi-user databases store and process transactions, often by using a transaction ID or XID.

There are multiple varying ways for transactions to be implemented other than the simple way documented above. Nested transactions, for example, are transactions which contain statements within them that start new transactions (i.e. sub-transactions). Multi-level transactions are a variant of nested transactions where the sub-transactions take place at different levels of a layered system architecture (e.g., with one operation at the database-engine level, one operation at the operating-system level). Another type of transaction is the compensating transaction.

In SQL
Transactions are available in most SQL database implementations, though with varying levels of robustness. (MySQL, for example, began supporting transactions from version 5.5, with the switch to the InnoDB storage engine. The previously used storage engine, MyISAM did not support transactions.)

A transaction is typically started using the command BEGIN (although the SQL standard specifies START TRANSACTION). When the system processes a COMMIT statement, the transaction ends with successful completion. A ROLLBACK statement can also end the transaction, undoing any work performed since BEGIN TRANSACTION. If autocommit was disabled using START TRANSACTION, autocommit will also be re-enabled at the transaction’s end.

One can set the isolation level for individual transactional operations as well as globally. At the READ COMMITTED level, the result of any work done after a transaction has commenced, but before it has ended, will remain invisible to other database-users until it has ended. At the lowest level (READ UNCOMMITTED), which may occasionally be used to ensure high concurrency, such changes will be visible.

Leave a Reply

Your email address will not be published. Required fields are marked *