A database system can be used to retain and process data in one or more tables without needing to write specific programs to run these functions. A Database Management System (DBMS) provides high-level commands to work on the data in the database. The DBMS allows the database administrator to control all components of the database, including its design, performance, and security. The following are examples of various types databases:
- Hierarchical databases – links structures in a tree structure. Each record is restricted to one owner thus a hierarchical database often cannot be correlated to structures in the real world.
- Mesh databases – more flexible than the hierarchical database, it employs a lattice structure where each record can have multiple parent and child records.
- Relational databases – consist of a collection of tables linked by their primary keys. Relational database designs are commonly used by organizations. Most relational databases use SQL as their query language.
- Object-oriented database – designed to resolve some of the limitations of large relational databases. Object-oriented databases don’t employ advanced language such as SQL but support modeling and the creation of data as objects.
Transaction Processing in Database Management
In database management, transaction management is required to mandate that only one user at a time can modify data and that transactions are valid and complete. When more than one user undergoes modification in the database at the same time, which is known as concurrency, controls must apply so that modifications affected by one user do not impact modifications affected by another user. This can be accomplished by data locking.
Data locking resolves the issues associated with concurrency and provides successful completion of all transactions. Locking also supports isolation of the transaction, allowing all transactions to run in complete separation from one another, even if multiple transactions can be running at any time. This is called serializability and accomplished by implementing a set of concurrent transactions equivalent to the database state that would be achieved if the set of transactions were executed serially, i.e., in sequential stages.
Serialization is an important aspect of processing transactions to ensure data in the database is correct at all times, however many transactions do not always require full isolation. If a transaction is prepared to accept inconsistent data, it’s labeled the isolation level and is the degree to which one transaction must be separated from other transactions. A lower isolation level increases concurrency at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct but can negatively affect concurrency. If the transaction is processed without any errors while being executed, modifications in the transaction become a permanent part of the database.
All transactions that include data alterations will transition to a new point of consistency and become committed or will be rolled back to the original state of consistency. Transactions are not left in an in-between state if the database is not consistent. All transactions must contain a logical unit of work exhibit four properties, called the ACID properties (Atomicity, Consistency, Isolation and Durability). These properties dictate that:
- A transaction must be an atomic unit of work, i.e., all of its data modifications are performed, or none are performed.
- When completed, a transaction must leave all data in a consistent state.
- In a relational database, all rules must be applied to the transaction’s modifications in order to maintain all data integrity and all internal data structures must be correct at the end of the transaction.
- Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions.
A transaction either sees data in its original state before another concurrent transaction modified it or it sees the data after the second transaction is completed, but it does not see an in-between state. This situation is referred to as serializability, because it results in the capability to reload the starting data and replay a series of transactions in order to end up with the data in the same state it was in after the original transactions were performed.
After a transaction has completed, its effects are permanently in place in the system. The modifications must persist even in the event of a system failure, i.e., they must be durable. When a transaction begins, DBMS must hold several resources to the end of the transaction to shield the ACID properties of the transaction. If data is altered, the modified rows must be protected with exclusive locks that block any other transaction from reading the rows, and exclusive locks must be retained until the transaction is committed or rolled back.
