MIDTERMS | Transaction and Concurrency Control | Advanced Database Systems

Transaction

A collection of operations that form a single logical unit of work. If one operation is not successful, all other operations will be undone.

Database request

The equivalent of a single SQL statement in an application program or transaction. If a transaction has three (3) update statements and one (1) insert statement, the transaction uses four (4) database requests.

Database consistent state

Satisfies the constraints specified in the schema.

Transaction log

A DBMS uses this to keep track of all transactions that update the database. In the case of system failure, this log helps bring the database back to a consistent state.

Atomicity

Requires that all operations (SQL requests) of a transaction should be completed. Example: Transaction T1 has four (4) SQL requests that must be successfully completed. Otherwise, the entire transaction is aborted.

Consistency

Ensures that only valid data following all rules and constraints will be written in the database. When a transaction results in invalid data, the database reverts to its previous state

Isolation

The data used during the execution of a current transaction cannot be used by another transaction until the first one is completed. Example: If two (2) people use the same ATM card and make a transaction at the same time, the first one to be connected will have its first transaction. Therefore, other accesses from that account would be locked until the existing session is over.

Durability

It ensures that once transaction changes are done and committed, they cannot be undone or lost.

Atomicity, Consistency, Isolation, Durability

Properties of transactions: HINT: A C I D

BEGIN TRANSACTION

Marks the beginning of transaction execution.

COMMIT

Signals a successful end of the transaction so that any changes (updates) executed by the transaction can be safely committed to the database and will not be undone.

ROLLBACK TRANSACTION

This signals that the transaction has ended unsuccessfully so that any changes or effects that the transaction may have applied to the database must be undone.

SAVE TRANSACTION

A point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

@@TRANCOUNT

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

BEGIN TRANSACTION, COMMIT, ROLLBACK TRANSACTION, SAVE TRANSACTION, @@TRANCOUNT

SQL Transactional Commands: HINT: BT C RT ST @@T

Active state

A transaction stays in this state to perform READ and WRITE operations.

Partially committed state

Where the final statement in queries has been executed.

Committed state

After all the operations have been completed.

Failed State

When one of the operations cannot be done or proceed.

Terminated

Corresponds to the transaction leaving the system and if it is either committed or aborted.

Active State, Partially Committed State, Committed State, Failed State, Terminated

Transaction Execution States: HINT: AS PCS CS FS T

Concurrency Control

When several transactions execute simultaneously in the database, there will be a chance that the consistency and integrity of data may no longer be preserved. The system must control the interaction among the concurrent transactions, and this control is achieved through ________ __________ techniques.

Lost Update

occurs when two concurrent transactions, T1 and T2, are updating the same data element, and one of the updates is lost (overwritten by the other transaction).

Uncommitted data

occurs when two transactions, T1 and T2, are executed concurrently, and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the ___________ ____.

Inconsistent retrievals

occur when a transaction accesses data before and after one or more other transactions finish working with the same data.

Lost Update, Uncommitted Data, Inconsistent Retrievals

The main problems in concurrent transactions: HINT: LU UD IR

Lock

It guarantees exclusive use of data item to a current transaction. Transaction T2 does not have access to a data item that is currently being used by transaction T1. The lock is released (unlocked) when the transaction is completed.

Database-level lock

Locks the entire database. Prevents the use of any tables in the database to transaction T2 while transaction T1 is being executed

Database-level lock

Good for batch processes, but not suitable for multiuser DBMS as it would slow down the data access if thousands of transactions had to wait for the current transaction to be completed.

Table-level lock

Locks the entire table. Prevents access to any row by transaction T2 while transaction T1 is using the table. If a transaction requires access to several tables, each table may be locked.

Table-level lock

Two (2) transactions can access the same database as long as they access different tables. Transactions T1 and T2 cannot access the same table even when they try to use different rows.

Row-level lock

Less restrictive. Allows concurrent transactions to access different rows of the same table even when the rows are located on the same page

Row-level lock

Improves the availability of data but requires high usage of space because a lock exists for each row in a table of the database.

Database-level lock, Table-level lock, Row-level lock

Concurrency Control with Locking Methods: HINT: DLL TLL RLL

Binary lock

Has only two states: locked (1) and unlocked (0). If an object such as a database, table, or row is locked by a transaction, no other transaction can use that object.

Binary lock

As a rule, the transaction must unlock the object after its termination. These options are automatically managed by the DBMS. User does not require to manually lock or unlock data items.

Shared/exclusive lock

An exclusive lock exists when access is reserved specifically for the transaction that locked the object. A shared lock exists when a transaction wants to read data from the database, and no exclusive lock has held that data item.

Unlocked, Shared(read), Exclusive(write)

Using the shared/exclusive locking concept, a lock can have three states: HINT: U S E

Binary Lock, Shared/Exclusive Lock

Lock Types: HINT: BL S/EL

Deadlock

Occurs when two transactions wait indefinitely for each other to unlock data. If T1 has not unlocked data item Y, T2 cannot begin. If T2 has not unlocked data item X, T1 cannot continue. Consequently, T1 and T2 wait for each other to unlock the required data item.

Deadlock prevention

A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur. If the transaction is aborted, all changes made by this transaction are rolled back, and all locks obtained by the transaction are released.

Deadlock detection

The DBMS periodically tests the database for deadlocks. If a deadlock is found, the “victim” transaction is aborted (rolled back and restarted), and another transaction continues.

Deadlock avoidance

The transaction must obtain all the locks it needs before it can be executed. However, the serial lock assignment required in ________ _________ increases action response time.

Deadlock Prevention, Deadlock Detection, Deadlock Avoidance

The three basic techniques to control deadlocks are: HINT: DP DD DA

Dirty Read

a transaction can read data that is not committed yet.

Non-repeatable read

a transaction reads a given row at time T1, and then it reads the same row at time T2, yielding different results. The original row may have been updated or deleted.

Phantom Read

a transaction executes a query at time T1, and then it runs the same query at time T2, yielding additional rows that satisfy the query.

Dirty Read, Non-Repeatable Read, Phantom Read

Transaction Isolation Level: HINT: DR NRR PR

READ UNCOMMITTED

It is the least restrictive isolation level

READ UNCOMMITTED

it ignores locks placed by other transactions.

READ UNCOMMITTED

Can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.

READ COMMITTED

Default isolation level for SQL Server

READ COMMITTED

Prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions

READ COMMITTED

Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.

REPEATABLE READ

A more restrictive isolation level than READ COMMITTED

REPEATABLE READ

Encompasses READ COMMITTED

REPEATABLE READ

Ensures that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits

REPEATABLE READ

Does not prevent other transactions from inserting new rows into the tables which have been using in the existing transaction, resulting in “phantom reads”

SERIALIZABLE

Ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction.

SERIALIZABLE

Most restrictive level and gives solution to the phantom read problem.

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Four (4) Transaction Isolation levels: HINT: RU RC RR S