Editing Database transaction (section)

==Transactional databases==
A '''transactional database''' is a [[DBMS]] that provides the [[ACID properties]] for a bracketed set of database operations (begin-commit). Transactions ensure that the database is always in a consistent state, even in the event of concurrent updates and failures.<ref>{{Cite journal |last1=DINCĂ |first1=Ana-Maria |last2=AXINTE |first2=Sabina-Daniela |last3=BACIVAROV |first3=Ioan |date=2022-12-29 |title=Performance Enhancements for Database Transactions |url=http://dx.doi.org/10.19107/ijisc.2022.02.02 |journal=International Journal of Information Security and Cybercrime |volume=11 |issue=2 |pages=29–34 |doi=10.19107/ijisc.2022.02.02 |s2cid=259653728 |issn=2285-9225|url-access=subscription }}</ref> All the write operations within a transaction have an all-or-nothing effect, that is, either the transaction succeeds and all writes take effect, or otherwise, the database is brought to a state that does not include any of the writes of the transaction. Transactions also ensure that the effect of concurrent transactions satisfies certain guarantees, known as [[isolation level]]. The highest isolation level is [[serializability]], which guarantees that the effect of concurrent transactions is equivalent to their serial (i.e. sequential) execution.

Most {{As of|2008|alt=modern}} [[relational database management system]]s support transactions. [[NoSQL]] databases prioritize scalability along with supporting transactions in order to guarantee data consistency in the event of concurrent updates and 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 system]]s consider [[Data consistency|consistency]] and [[data integrity|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 error occurs, then commit the transaction.
# If an error occurs, then roll back the transaction.

A transaction commit operation persists all the results of data manipulations within the scope of the transaction to the database. A transaction rollback operation does not persist the partial results of data manipulations within the scope of the transaction 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 [[identifier|ID]] or XID.

There are multiple varying ways for transactions to be implemented other than the simple way documented above. [[Nested transaction]]s, 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).<ref>{{cite journal |last1=Beeri |first1=C. |last2=Bernstein |first2=P. A. |last3=Goodman |first3=N. |title=A model for concurrency in nested transactions systems |journal=Journal of the ACM |volume=36 |issue=1 |pages=230–269 |year=1989 |doi=10.1145/62044.62046|s2cid=12956480 |doi-access=free }}</ref> Another type of transaction is the [[compensating transaction]].

===In SQL===
Transactions are available in most SQL database implementations, though with varying levels of robustness. For example, [[MySQL]] began supporting transactions from early version 3.23, but the [[InnoDB]] storage engine was not default before version 5.5. The earlier available storage engine, [[MyISAM]] does not support transactions.

A transaction is typically started using the command <code>BEGIN</code> (although the SQL standard specifies <code>START TRANSACTION</code>). When the system processes a <code>[[Commit (SQL)|COMMIT]]</code> statement, the transaction ends with successful completion.  A <code>[[Rollback (data management)|ROLLBACK]]</code> statement can also end the transaction, undoing any work performed since <code>BEGIN</code>. If [[autocommit]] was disabled with the start of a transaction, autocommit will also be re-enabled with the end of the transaction.

One can set the [[Isolation (database systems)|isolation level]] for individual transactional operations as well as globally. At the highest level (<code>READ COMMITTED</code>), the result of any operation performed after a transaction has started will remain invisible to other database users until the transaction has ended. At the lowest level (<code>READ UNCOMMITTED</code>), which may occasionally be used to ensure high concurrency, such changes will be immediately visible.