DBMS Transaction Management

Comments · 6617 Views

DBMS Transaction Management is an important concept while managing databases and I wrote more about the concept.

ABSTRACT

A transaction is a single unit of handling in a DBMS. A transaction may or may not change what’s in the database. However, sometimes when the concurrent transactions occur it can cause hardware or system failure.

The transaction states are Active, Partially Committed, Committed, Failed, and Terminate. All transactions follow ACID properties that stand for Atomicity, Consistency, Isolation, and Durability. There are three DBMS transaction types are based on its application areas, its action/its operation, and the structure of the transaction. Many times, a schedule is created which is a group of several concurrent transactions that are being executed one after the other. Serializability is another important process when two concurrent schedules have equal output when the transaction executes on after the other.

1 Transactions

A transaction is a logical unit of work in a database that contains several SQL statements that can perform operations like insert, delete, modify, and retrieve or create, drop, rename, or alter. A database transaction can be correlated with a real-world event. When all the database operations are held between the beginning and end transaction statements, they are considered a single transaction. Every transaction has a unique identifier assigned to it called transaction ID.

There are two different effects that can take place on a transaction:

  • Commit: This means that the administrator that made the change on the database is explicitly or implicitly informed the database to make the changes addressed in a transaction permanent.
  1. Explicit Commit: When a user issues a commit statement in the transaction.
  2. Implicit Commit: This occurs after normal termination of an application or completion of an operation.

The changes made by the SQL statements in the transaction are visible to other users only after this transaction commits the changes. The statements issued after the previous transaction commits can be seen by the statements.

Before a transaction changes data, the following happens:

  1. Undo information is generated that contains old values.
  2. Redo log entries are created.

During a transaction, the following happens:

  1. The transaction table records the change in data.
  2. The database releases the locks held.
  3. The database marks the transaction complete.
  • Rollback: At any time, a statement in a transaction causes an error, all the previous effects of the statements before this error are rolled back. This is called a statement-level rollback. An example when this could occur is when a statement is inserting a duplicate value in a primary key of the database. Other examples could be when there are multiple SQL statements in a deadlock, syntax errors, etc.

A statement causes loss of work from where it failed, and any does not cause loss of any work before it in that current transaction. In the case of an implicit commit, the statement immediately preceded it in undone.

The different types of rollbacks are:

  1. Statement level
  2. To a save point
  3. Per-user request
  4. Due to abnormal termination
  5. Due to incomplete transactions

Resumable Space Allocation

This feature is useful for the execution of large operations when an error occurs. Here, the database suspends the execution instead of returning it as an error and automatically resumes once the administrator fixes the error in the statement.  When a statement is suspended, the transaction is suspended, and all the resources held in the transaction are suspended and resumed.

This feature is suspended only when one of the following conditions occur:

  1. Out of space
  2. Maximum extents reached
  3. Space exceeded

The resumable mode is applicable only when it is explicitly enabling for each session using the “ALTER SESSION” statement.

The below image translation depicts how a transaction is carried out:

Figure 1: What a transaction looks like, [citation: guru99]

At the beginning and at the end of a transaction, the database is at a consistent state, however, during the transaction, the database may be in a temporarily inconsistent state as due to the several SQL statements present in the transaction, there are possible changes that are being made on the database which leads to this inconsistency.

The transaction ends in the following cases:

  • A commit, rollback, create, drop, rename, alter statement is issued without a save point condition.
  • A user disconnects from the system.
  • A process terminates abnormally.

Example. Consider a bank database. A customer asks a bank employee to transfer money from one account he has in the bank to another. He wants to transfer money from his checking account to his savings account. The following steps take place during this transfer:

  • The balance decreases in the checking account and is sent to the savings account.
  • The money is received from the checking account and the balance increases in the savings account.
  • This transaction between both the checking and the savings account is recorded in the bank database.

The above set of steps can be implemented as the following pseudocode when a customer wants to transfer $1000 from his checking account to his savings account:

 

Checking Account

UPDATE CheckingAccount

ExistingBalance = CheckingAccount.Balance

NewBalance = ExistingBalance  – 1000

CheckingAccount.Balance = NewBalance

 

Savings Account

UPDATE SavingsAccount

ExistingBalance = SavingsAccount.Balance

NewBalance = ExistingBalance + 1000

SavingsAccount.Balance = NewBalance

 

COMMIT

1.1 Data Concurrency

In a multiple user database, there are several statements with multiple transactions can lead to users updating the same data. Data concurrency is the accessibility of data by several users which can avoid the change of data improperly and doesn’t comprise data integrity.

Example. To avoid a user, delete a column in a table when another user is updating the same column.

To achieve this a lock is placed on the user who tries to access the same component when another user is making changes to the same component. This maintains data integrity by also allowing the maximum level of access by multiple users.

1.2 Data Consistency

Every user that accesses the data has a consistent view, including the changes that are being made in the transaction.

Example. The problem where one transaction sees the uncommitted changes by another transaction, to prevent dirty read.

When a query returns are committed and consistent, a statement-level read consistency is achieved. Based on the isolation level, this feature is implemented from when the transaction begins. A transaction also provides read level consistency. Every statement in a transaction views the data from the top to bottom approach from the beginning to the end of the transaction.

1.2.1. Multiversion Read Consistency

This is the ability to materialize several versions of data.

  • Read consistent queries: The data that is sent after a query is committed and consistent.
  • Nonblocking queries: Users of the data don’t block the queries.

1.2.2. Statement Level Read Consistency

The data returned by a query is committed and consistent. For a statement to be consistent, it depends on the level of isolation and the query’s nature:

  • Read committed isolation level: The time at which the statement is opened, like a select statement.
  • Serializable or read-only transaction: If multiple select statements occur at the beginning of each transaction.

1.2.3. Transaction Level Read Consistency

Every transaction sees the data from the same point when the transaction begins every query that is made serializable sees changes made by the transaction itself.

1.3 Statements in a Transaction

1.3.1. Transaction Control Statements

  • COMMIT: Make permanent changes to a transaction.
  • ROLLBACK: Undo the changes in a transaction
  • ROLLBACK TO SAVEPOINT: Undo the changes to a save point.
  • SAVEPOINT: Set a point to which the transaction rollback to.
  • SET TRANSACTION: Define the properties of a transaction.
  • SET CONSTRAINT: Check if the following condition satisfies in a transaction.

1.3.2. Session Control Statements

  • ALTER SESSION: Perform a function to alter the current session.
  • SET ROLE: Establish the privileges of different users.

1.3.3. SQL Statements

  • “Define, allocate, and release a cursor (DECLARE CURSOR, OPEN, CLOSE).
  • Specify a database and connect to it (DECLARE DATABASE, CONNECT).
  • Assign variable names (DECLARE STATEMENT).
  • Initialize descriptors (DESCRIBE).
  • Specify how error and warning conditions are handled (WHENEVER).
  • Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE).
  • Retrieve data from the database (FETCH).” [Oracle]

1.4 States of a Transaction

 

 

Figure 2: The process of transactions from the begin to the end, [citation: guru99]

[guru99]

The steps that a transaction follow is below:

  • After a transaction state is executed it becomes active and can issue a read or a write operation.
  • After the read and write operations are completed the transaction goes into a partially committed state.
  • After making sure that all the safety protocols are followed and the system failure will not result in the inability to record changes in the transaction permanent permanently, the transaction commits and enters a committed state.
  • If the safety protocols find an issue the transaction goes to a failed state.
  • In the meantime, if the transaction is aborted when it's in the active state, the transaction then also goes to a failed state and it rolls back to undo the effect of the right operations.
  • When the transaction leaves the system, the terminated state is issued.

1.5 ACID Properties

              A transaction in a database maintains the following properties:

1.5.1 Atomicity. The transaction must be treated as an atomic unit well all the operations are executed as a whole or there aren't any operations executed at all. There is no state where a transaction in the database is partially executed.

1.5.2. Consistency. The database should stay in a consistent state after any transaction. The data that is already in the database should not have any effect after a transaction the transaction must remain in a consistent state before the execution of a transaction and after it as well.

1.5.3. Durability. The database should be durable to hold all the latest updates even though the system fails. In the case where a transaction updates the data in the database and commits it. the database should hold the modified data. In the case where a transaction commits but the system fails before the data could be returned to a disk data will be updated once the system is back and connected. 

1.5.4. Isolation. Sometimes in a database, there is more than one transaction that is executed simultaneously. Isolation makes sure that all the transactions that are carried out are executed as if it is the only transaction that is being run on the system and the other transactions will not affect the existing transaction.

1.6 Types of Transactions

1.6.1. Based on Application Areas.

  • Non-distributed vs. distributed
  • Compensating transactions
  • Transactions Timing
  • On-line vs. batch

1.6.2. Based on Actions.

  • Two-step
  • Restricted
  • Action model

1.6.3. Based on Structure

  • Flat or simple transactions: Sequence of primitive operations.
  • Nested transactions: A transaction contains other transactions.
  • Workflow

1.7 Schedules

A schedule is a process where a group of multiple parallel transactions is created, and it's executed one after the other. This also preserves the order when the instructions appear in each transaction and if two transactions appear at the same time the result of one transaction may affect the output or result of another transaction.

There might be instances where for example, a transaction has outdated information about something and will read that information to the second transaction however since it is run after the second transaction the new values of the first transaction might not come into play. Parallel execution of transactions is permitted by having an equivalence relation must be established among simultaneously running transactions:

1.7.1. Result Equivalence. If two schedules show the same result after execution, they're called a result equivalence schedule and they need not show the same result for a different set of values.

1.7.2. View Equivalence. When a transaction in both the schedules performs a similar action but in two different schedules this is called view equivalence schedule where the transactions are the same, but the tables are different.

1.7.3. Conflict Equivalence. Two transactions update or view the same data and this causes conflict as the order of execution of the transactions could affect the output.

1.8 Serializability

Serializability is the search for a concurrent schedule whose output is equal to the serial schedule where every transaction executes one after the other according to a schedule.

The two types of transactions are:

  • Conflict
  • View

1.8 Naming Transactions

A simple approach should be taken while naming a transaction. The name of the transaction should be regarding what it’s about. If transactions are given accurate names, it makes it easier to monitor the transaction and understand which transaction has the error.

A transaction is named using the SET TRANSACTION … NAME statement before the transaction is started. Every transaction has a transaction ID that is unique to it in the case where two transactions are named the same.

1.8 Types of Transactions

1.8.1. Autonomous Transactions.

An independent transaction that is called from the main transaction is an autonomous transaction. In an autonomous transaction, the calling transaction can be suspended or resumed and SQL, commit or undo operations can be performed.

Example. In a stock purchase, the customer data must be committed irrespective if the stock goes through or not. Error messages should be recorded in a log table even though the transaction rolls back.

  • The autonomous transactions can’t see uncommitted changes that are made by the main transaction and don’t share locks or resources with the main transaction.
  • Once the autonomous transactions are committed, changes in the autonomous transaction are visible to other transactions.
  • Autonomous transactions can begin other autonomous transactions. The only limit other than resource limits is the number of levels of autonomous transactions that can be called.

1.8.2. Distributed Transactions.

A transaction that includes one or more statements that update data on two or more distinct nodes on a distributed database is called distributed transactions. A distributed database is a group of databases in a system that appears as a single data source.

A distributed database alters data on different databases. This type of transaction must coordinate the commit or rollback of changes in a transaction as a single unit. An entire distribute transaction must commit or rollback.

For a distributed database, a transaction control must maintain consistency even if a network or system fails.

ACKNOWLEDGMENTS

 I would like to thank the authors of the below-referenced materials for writing a well thought out and in detail description and analysis of this topic to further assist me in writing this research paper.

REFERENCES

[1]   Lance Ashdown, Tom Kyte, Joe McCormack, 2019. Oracle Database Database Concepts. (1993, 2019). DOI: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/database-concepts.pdf

[2]   Oracle Help Center, 2019. Oracle Database Online Documentation 10g Release 2 (10.2). (2019).

DOI: https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm

 [3]   Prerana Jain, 2018. Transaction in Database Management System. (June 2018). DOI:https://www.includehelp.com/dbms/transaction-in-database-management-system.aspx

[4]   TutorialsPoint, 2019. DBMS – Transaction. (2019).

DOI: https://www.tutorialspoint.com/dbms/dbms_transaction.htm

[5]   Guru99, 2019. DBMS Transaction Management: ACID Properties, Schedule. (2019). DOI: https://www.guru99.com/dbms-transaction-management.html

Comments