A database transaction symbolizes a unit of work, performed within a
database management system
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases span ...
(or similar system) against a
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
, 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:
# 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.
# 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 be
atomic (it must either be complete in its entirety or have no effect whatsoever),
consistent
In classical deductive logic, a consistent theory is one that does not lead to a logical contradiction. The lack of contradiction can be defined in either semantic or syntactic terms. The semantic definition states that a theory is consistent i ...
(it must conform to existing constraints in the database),
isolated
Isolation is the near or complete lack of social contact by an individual.
Isolation or isolated may also refer to:
Sociology and psychology
*Isolation (health care), various measures taken to prevent contagious diseases from being spread
**Is ...
(it must not affect other transactions) and
durable
Durability is the ability of a physical product to remain functional, without requiring excessive maintenance or repair, when faced with the challenges of normal operation over its design lifetime. There are several measures of durability in use, ...
(it must get written to persistent storage). Database practitioners often refer to these properties of database transactions using the acronym
ACID
In computer science, ACID ( atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps. In the context of databases, a sequ ...
.
Purpose
Database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
s and other data stores which treat the
integrity
Integrity is the practice of being honest and showing a consistent and uncompromising adherence to strong moral and ethical principles and values.
In ethics, integrity is regarded as the honesty and truthfulness or accuracy of one's actions. Inte ...
of 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 from
double-entry accounting systems often 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:
# Debit $100 to Groceries Expense Account
# 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
A transactional database is a
DBMS
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases spa ...
that provides the
ACID properties for a bracketed set of database operations (begin-commit). 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
In concurrency control of databases, Philip A. Bernstein, Vassos Hadzilacos, Nathan Goodman (1987)''Concurrency Control and Recovery in Database Systems''(free PDF download), Addison Wesley Publishing Company, Gerhard Weikum, Gottfried Vossen (200 ...
, which guarantees that the effect of concurrent transactions is equivalent to their serial (i.e. sequential) execution.
Most
relational database management system
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
s support transactions.
NoSQL
A NoSQL (originally referring to "non- SQL" or "non-relational") database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases. Such databases have existed ...
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
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases span ...
s consider
consistency
In classical deductive logic, a consistent theory is one that does not lead to a logical contradiction. The lack of contradiction can be defined in either semantic or syntactic terms. The semantic definition states that a theory is consistent ...
and
integrity
Integrity is the practice of being honest and showing a consistent and uncompromising adherence to strong moral and ethical principles and values.
In ethics, integrity is regarded as the honesty and truthfulness or accuracy of one's actions. Inte ...
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
ID or XID.
There are multiple varying ways for transactions to be implemented other than the simple way documented above.
Nested transaction A nested transaction is a database transaction that is started by an instruction within the scope of an already started transaction.
Nested transactions are implemented differently in different databases. However, they have in common that the chang ...
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). Another type of transaction is the
compensating transaction
The execution of a business process consists of one or more transactions. Each transaction may consist of several individual operations yet, as a whole, it moves the system between consistent states.
There are two groups of systems where compens ...
.
In SQL
Transactions are available in most SQL database implementations, though with varying levels of robustness. For example,
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database o ...
began supporting transactions from early version 3.23, but the
InnoDB
InnoDB is a database engine, storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type. It provides the standard ACID-compliant Database transact ...
storage engine was not default before version 5.5. The earlier available storage engine,
MyISAM
MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009. It is based on the older ISAM code, but it has many useful extensions.
Filesystem
Each MyISAM table is s ...
does 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
In political science, rollback is the strategy of forcing a change in the major policies of a state, usually by replacing its ruling regime. It contrasts with containment, which means preventing the expansion of that state; and with détente, w ...
statement can also end the transaction, undoing any work performed since
BEGIN
. If
autocommit
In the context of data management, autocommit is a mode of operation of a database connection. Each individual database interaction (i.e., each SQL statement) submitted through the database connection in autocommit mode will be executed in its own ...
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 level for 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
Relational databases are traditionally composed of tables with fixed-size fields and records. Object databases comprise variable-sized
blobs, possibly
serializable or incorporating a
mime-type
A media type (also known as a MIME type) is a two-part identifier for file formats and format contents transmitted on the Internet. The Internet Assigned Numbers Authority (IANA) is the official authority for the standardization and publication o ...
. The fundamental similarities between Relational and Object databases are the start and the
commit or
rollback
In political science, rollback is the strategy of forcing a change in the major policies of a state, usually by replacing its ruling regime. It contrasts with containment, which means preventing the expansion of that state; and with détente, w ...
.
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 back
atomically, such that at the end of the transaction there is no
inconsistency.
Distributed transactions
Database systems implement
distributed transaction A distributed transaction is a database transaction in which two or more network hosts are involved. Usually, hosts provide transactional resources, while the transaction manager is responsible for creating and managing a global transaction that enc ...
s 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.
Transactional filesystems
The
Namesys
Namesys was a California corporation responsible for the design and implementation of the ReiserFS and Reiser4 filesystems. It has been inactive since late 2007, and , is listed with the State of California with a status of "Suspended". Owned by Ha ...
Reiser4
Reiser4 is a computer file system, successor to the ReiserFS file system, developed from scratch by Namesys and sponsored by DARPA as well as Linspire. Reiser4 was named after its former lead developer Hans Reiser. , the Reiser4 patch set is stil ...
filesystem for
Linux
Linux ( or ) is a family of open-source Unix-like operating systems based on the Linux kernel, an operating system kernel first released on September 17, 1991, by Linus Torvalds. Linux is typically packaged as a Linux distribution, which ...
supports transactions, and as of
Microsoft
Microsoft Corporation is an American multinational technology corporation producing computer software, consumer electronics, personal computers, and related services headquartered at the Microsoft Redmond campus located in Redmond, Washing ...
Windows Vista
Windows Vista is a major release of the Windows NT operating system developed by Microsoft. It was the direct successor to Windows XP, which was released five years before, at the time being the longest time span between successive releases of ...
, the Microsoft
NTFS
New Technology File System (NTFS) is a proprietary journaling file system developed by Microsoft. Starting with Windows NT 3.1, it is the default file system of the Windows NT family. It superseded File Allocation Table (FAT) as the preferred fil ...
filesystem supports
distributed transaction A distributed transaction is a database transaction in which two or more network hosts are involved. Usually, hosts provide transactional resources, while the transaction manager is responsible for creating and managing a global transaction that enc ...
s across networks. There is occurring research into more data coherent filesystems, such as the
Warp Transactional Filesystem
Warp, warped or warping may refer to:
Arts and entertainment Books and comics
* WaRP Graphics, an alternative comics publisher
* ''Warp'' (First Comics), comic book series published by First Comics based on the play ''Warp!''
* Warp (comics), a ...
(WTF).
[https://www.usenix.org/system/files/conference/nsdi16/nsdi16-paper-escriva.pdf ]
See also
*
Concurrency control
In information technology and computer science, especially in the fields of computer programming, operating systems, multiprocessors, and databases, concurrency control ensures that correct results for Concurrent computing, concurrent operations a ...
*
Post void
References
Further reading
*
Philip A. Bernstein, Eric Newcomer (2009)
''Principles of Transaction Processing'', 2nd Edition
Morgan Kaufmann (Elsevier),
* Gerhard Weikum, Gottfried Vossen (2001), ''Transactional information systems: theory, algorithms, and the practice of concurrency control and recovery'', Morgan Kaufmann,
External links
*
c2:TransactionProcessing
* https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT016
* https://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm
{{DEFAULTSORT:Database Transaction
Data management
Transaction processing