Transactional Database Operations
A transactional database is a DBMS that supports the rolling back of transactions on the data stores if not completed properly. An interruption in the transaction can be caused by power loss or an interruption in connectivity to the data store. The majority of databases in use today support transactions. These operations can consist of one or many data manipulation queries or statements. Since data integrity and consistency are critical to proper database operation, most transactions use SQL or a SQL-like language to conduct operations using the following pattern:
Step 1 – Initiate the transaction.
Step 2 – Execute the provided set of data queries or manipulations.
Step 3 – Commit the transaction and complete it if there are no errors.
Step 4 – If an error occurs in the transaction, roll it back and end the operation.
In multi-user databases, transactions will often store transactions using an XID, or transaction ID. There are a number of ways that companies implemented transactional databases in practice today to include the use of nested and multi-level transactions.