Database transaction
Encyclopedia
A transaction comprises 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. Transactions in a database environment have two main purposes:
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.
s and other data stores which treat the integrity
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 system
s 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:
A transactional system would make both entries — 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.
Most relational database management system
s fall into the category of databases that support transactions.
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 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:
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(Article by Vishak P Nair).
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 similar but have a few extra properties. Another type of transaction is the compensating transaction
.
is inherently transactional, and a transaction is automatically started when another ends. Some databases extend SQL and implement a
.[citation needed]
The result of any work done after this point will remain invisible to other database-users until the system processes a
was disabled using
Some database systems allow the synonyms
s as transactions against multiple applications or hosts. A distributed transaction enforces the ACID properties over multiple systems or data stores, and might include systems such as databases, file systems, messaging systems, and other applications. In a distributed transaction a coordinating service ensures that all parts of the transaction are applied to all relevant systems. As with database and other transactions, if any part of the transaction fails, the entire transaction is rolled back across all affected systems.
Reiser4
filesystem for Linux
supports transactions, and as of Microsoft
Windows Vista
, the Microsoft NTFS
filesystem supports distributed transactions across networks.
Database management system
A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...
(or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. 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 outcome 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
ACID
In computer science, ACID is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction...
.
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.
Purpose
DatabaseDatabase
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
s and other data stores which treat the integrity
Data integrity
Data Integrity in its broadest meaning refers to the trustworthiness of system resources over their entire life cycle. In more analytic terms, it is "the representational faithfulness of information to the true state of the object that the information represents, where representational faithfulness...
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 system
Double-entry bookkeeping system
A double-entry bookkeeping system is a set of rules for recording financial information in a financial accounting system in which every transaction or event changes at least two different nominal ledger accounts....
s 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 — 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 where write transactions on the database are able to be rolled back if they are not completed properly (e.g. due to power or connectivity loss).Most relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
s fall into the category of databases that support transactions.
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
Database system
A database system is a term that is typically used to encapsulate the constructs of a data model, database Management system and database....
s consider consistency
Data consistency
Data consistency summarizes the validity, accuracy, usability and integrity of related data between applications and across an IT enterprise. This ensures that each user observes a consistent view of the data, including visible changes made by the user's own transactions and transactions of other...
and integrity
Data integrity
Data Integrity in its broadest meaning refers to the trustworthiness of system resources over their entire life cycle. In more analytic terms, it is "the representational faithfulness of information to the true state of the object that the information represents, where representational faithfulness...
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 rollback 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
Rollback (data management)
In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed...
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(Article by Vishak P Nair).
Internally, multi-user databases store and process transactions, often by using a transaction ID
Identifier
An identifier is a name that identifies either a unique object or a unique class of objects, where the "object" or class may be an idea, physical [countable] object , or physical [noncountable] substance...
or XID.
There are multiple varying ways for transactions to be implemented other than the simple way documented above. Nested transactions
Nested transaction
With reference to a database transaction, a nested transaction occurs when a new transaction is started by an instruction that is already inside an existing transaction. The new transaction is said to be nested within the existing transaction, hence the term....
, for example, are transactions which contain statements within them that start new transactions (i.e. sub-transactions). Multi-level transactions are similar but have a few extra properties. Another type of transaction is the compensating transaction
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....
.
In SQL
SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
is inherently transactional, and a transaction is automatically started when another ends. Some databases extend SQL and implement a
START TRANSACTION statement, but while seemingly signifying the start of the transaction it merely deactivates autocommitAutocommit
In the context of data management, autocommit is a mode of operation of a database connection. Each individual database interaction submitted through the database connection in autocommit mode will be executed in its own transaction that is implicitly committed. An SQL statement executed in...
.[citation needed]
The result of any work done after this point will remain invisible to other database-users until the system processes a
COMMIT statement. A ROLLBACKRollback (data management)In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed...
statement can also occur, which will undo any work performed since the last transaction. Both COMMIT and ROLLBACK will end the transaction, and start anew. If autocommitAutocommit
In the context of data management, autocommit is a mode of operation of a database connection. Each individual database interaction submitted through the database connection in autocommit mode will be executed in its own transaction that is implicitly committed. An SQL statement executed in...
was disabled using
START TRANSACTION, autocommit will often also be reenabled.Some database systems allow the synonyms
BEGIN, BEGIN WORK and BEGIN TRANSACTION, and may have other options available.Distributed transactions
Database systems implement distributed transactionDistributed transaction
A distributed transaction is an operations bundle, 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 encompasses all operations against such resources...
s as transactions against multiple applications or hosts. A distributed transaction enforces the ACID properties over multiple systems or data stores, and might include systems such as databases, file systems, messaging systems, and other applications. In a distributed transaction a coordinating service ensures that all parts of the transaction are applied to all relevant systems. As with database and other transactions, if any part of the transaction fails, the entire transaction is rolled back across all affected systems.
Transactional filesystems
The NamesysNamesys
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 as of 2010, is listed with the State of California with a status of "Suspended". Owned by Hans Reiser, Namesys was based in Oakland,...
Reiser4
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...
filesystem for Linux
Linux
Linux is a Unix-like computer operating system assembled under the model of free and open source software development and distribution. The defining component of any Linux system is the Linux kernel, an operating system kernel first released October 5, 1991 by Linus Torvalds...
supports transactions, and as of Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
Windows Vista
Windows Vista
Windows Vista is an operating system released in several variations developed by Microsoft for use on personal computers, including home and business desktops, laptops, tablet PCs, and media center PCs...
, the Microsoft NTFS
NTFS
NTFS is the standard file system of Windows NT, including its later versions Windows 2000, Windows XP, Windows Server 2003, Windows Server 2008, Windows Vista, and Windows 7....
filesystem supports distributed transactions across networks.
Further reading
- Philip A. Bernstein, Eric Newcomer (2009): Principles of Transaction Processing, 2nd Edition, Morgan Kaufmann (Elsevier), ISBN 978-1-55860-623-4
- Gerhard Weikum, Gottfried Vossen (2001), Transactional information systems: theory, algorithms, and the practice of concurrency control and recovery, Morgan Kaufmann, ISBN 1558605088
External links
- Wiki:TransactionProcessing