Jump to content

Database transaction

From Wikipedia, the free encyclopedia

Adatabase transactionsymbolizes aunit of work,performed within adatabase management system(or similar system) against adatabase,that is 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:

  1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure. For example: when execution prematurely and unexpectedly stops (completely or partially) in which case many operations upon a database remain uncompleted, with unclear status.
  2. To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.

In a database management system, a transaction is a single unit of logic or work, sometimes made up of multiple operations. Any logical calculation done in a consistent mode in a database is known as a transaction. One example is a transfer from one bank account to another: the complete transaction requires subtracting the amount to be transferred from one account and adding that same amount to the other.

A database transaction, by definition, must beatomic(it must either be complete in its entirety or have no effect whatsoever),consistent(it must conform to existing constraints in the database),isolated(it must not affect other transactions) anddurable(it must get written to persistent storage).[1]Database practitioners often refer to these properties of database transactions using the acronymACID.

Purpose[edit]

Databasesand other data stores which treat theintegrityof data as paramount often include the ability to handle transactions to maintain the integrity of data. A single transaction consists of one or more independent units of work, each reading and/or writing information to a database or other data store. When this happens it is often important to ensure that all such processing leaves the database or data store in a consistent state.

Examples fromdouble-entry accounting systemsoften illustrate the concept of transactions. In double-entry accounting every debit requires the recording of an associated credit. If one writes a check for $100 to buy groceries, a transactional double-entry accounting system must record the following two entries to cover the single transaction:

  1. Debit $100 to Groceries Expense Account
  2. Credit $100 to Checking Account

A transactional system would make both entries pass or both entries would fail. By treating the recording of multiple entries as an atomic transactional unit of work the system maintains the integrity of the data recorded. In other words, nobody ends up with a situation in which a debit is recorded but no associated credit is recorded, or vice versa.

Transactional databases[edit]

Atransactional databaseis aDBMSthat provides theACID propertiesfor 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.[2]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 asisolation level.The highest isolation level isserializability,which guarantees that the effect of concurrent transactions is equivalent to their serial (i.e. sequential) execution.

Most modernrelational database management systemssupport transactions.NoSQLdatabases 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 ofdatabase systemsconsiderconsistencyandintegrityof data as highly important. A simple transaction is usually issued to the database system in a language likeSQLwrapped in a transaction, using a pattern similar to the following:

  1. Begin the transaction.
  2. Execute a set of data manipulations and/or queries.
  3. If no error occurs, then commit the transaction.
  4. 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 transactionIDor 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 transactionsare 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).[3]Another type of transaction is thecompensating transaction.

In SQL[edit]

Transactions are available in most SQL database implementations, though with varying levels of robustness. For example,MySQLbegan supporting transactions from early version 3.23, but theInnoDBstorage engine was not default before version 5.5. The earlier available storage engine,MyISAMdoes not support transactions.

A transaction is typically started using the commandBEGIN(although the SQL standard specifiesSTART TRANSACTION). When the system processes aCOMMITstatement, the transaction ends with successful completion. AROLLBACKstatement can also end the transaction, undoing any work performed sinceBEGIN.Ifautocommitwas disabled with the start of a transaction, autocommit will also be re-enabled with the end of the transaction.

One can set theisolation levelfor individual transactional operations as well as globally. At the highest level (READ COMMITTED), 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 (READ UNCOMMITTED), which may occasionally be used to ensure high concurrency, such changes will be immediately visible.

Object databases[edit]

Relational databases are traditionally composed of tables with fixed-size fields and records. Object databases comprise variable-sizedblobs,possiblyserializableor incorporating amime-type.The fundamental similarities between Relational and Object databases are the start and thecommitorrollback.

After starting a transaction, database records or objects are locked, either read-only or read-write. Reads and writes can then occur. Once the transaction is fully defined, changes are committed or rolled backatomically,such that at the end of the transaction there is noinconsistency.

Distributed transactions[edit]

Database systems implementdistributed transactions[4]as transactions accessing data over multiple nodes. A distributed transaction enforces the ACID properties over multiple nodes, and might include systems such as databases, storage managers, file systems, messaging systems, and other data managers. In a distributed transaction there is typically an entity coordinating all the process to ensure that all parts of the transaction are applied to all relevant systems. Moreover, the integration of Storage as a Service (StaaS) within these environments is crucial, as it offers a virtually infinite pool of storage resources, accommodating a range of cloud-based data store classes with varying availability, scalability, and ACID properties. This integration is essential for achieving higher availability, lower response time, and cost efficiency in data-intensive applications deployed across cloud-based data stores.[5]

Transactional filesystems[edit]

TheNamesysReiser4filesystem forLinux[6]supports transactions, and as ofMicrosoftWindows Vista,the MicrosoftNTFSfilesystem[7]supportsdistributed transactionsacross networks. There is occurring research into more data coherent filesystems, such as theWarp Transactional Filesystem(WTF).[8]

See also[edit]

References[edit]

  1. ^"What is a Transaction? (Windows)".msdn.microsoft.7 January 2021.
  2. ^DINCĂ, Ana-Maria; AXINTE, Sabina-Daniela; BACIVAROV, Ioan (2022-12-29)."Performance Enhancements for Database Transactions".International Journal of Information Security and Cybercrime.11(2): 29–34.doi:10.19107/ijisc.2022.02.02.ISSN2285-9225.S2CID259653728.
  3. ^Beeri, C.; Bernstein, P. A.; Goodman, N. (1989)."A model for concurrency in nested transactions systems".Journal of the ACM.36(1): 230–269.doi:10.1145/62044.62046.S2CID12956480.
  4. ^Özsu, M. Tamer; Valduriez, Patrick (2011).Principles of Distributed Database Systems, Third Edition.Springer.Bibcode:2011podd.book.....O.doi:10.1007/978-1-4419-8834-8.ISBN978-1-4419-8833-1.
  5. ^Mansouri, Yaser; Toosi, Adel Nadjaran; Buyya, Rajkumar (2017-12-11)."Data Storage Management in Cloud Environments: Taxonomy, Survey, and Future Directions".ACM Computing Surveys.50(6): 91:1–91:51.doi:10.1145/3136623.ISSN0360-0300.
  6. ^"Linux.org".Linux.org.
  7. ^"MSDN Library".4 February 2013.Retrieved16 October2014.
  8. ^https:// usenix.org/system/files/conference/nsdi16/nsdi16-paper-escriva.pdf[bare URL PDF]

Further reading[edit]

External links[edit]

Retrieved from "https://en.wikipedia.org/w/index.php?title=Database_transaction&oldid=1210338108"