INFOTECH 31 (Transaction Management and Concurrency Control)

for it31 haha..

Transaction

It is a logical unit of work that must be entirely completed or entirely aborted; there are no intermediate states.

Atomicity

it requires the completion of all operations and if not, it aborts the transaction.

Consistency

requires the permanence of the database’s consistent state. It aborts any transaction if parts of the transaction violate the integrity constraint.

Isolation

if a transaction is using any data another transaction cannot use it.

Durability

the DBMS cannot undo a committed data – even in the event of a system failure.

Serializability

this ensures that the schedule of concurrent transaction yields consistent result

Transaction Management with SQL

Commit or Rollback terminates a transaction.

Transaction Log

DBMSs maintains a transaction log to keep track of all transactions that update the database.

Concurrency Control

It is the coordination of simultaneous execution of transactions in a multiuser database environment.

Lost Update

This problem occurs when two concurrent transactions, say T1 and T2, are updating the same data element.

Inconsistent Retrievals

Occurs when a transaction access data before and after another transaction finish working with such data.

Scheduler

Is a special DBMS process that establishes the order in which the operations within concurrent transactions are executed.

Concurrency Control with Locking Methods

Lock guarantees exclusive use of the data item to the current transaction.

Lock Granularity

it indicates the level of lock in use. These levels include database, table, page, row or field.

Database Level

The transaction locks the entire database preventing other transaction access to tables even if the current transaction is not using it.

Table Level

Transaction T1 locks the entire table preventing other transactions (T2, T3, etc.) to have access to some of the table rows

Page Level

Transaction T1 locks the entire diskpage

Row Level

Transaction T1 locks the required row.

Field Level

This allows concurrent transactions to access the same row as long as they require the use of different fields (attributes) within that row.

Binary Locks

It has two states: locked (1) or unlocked (0)Shared/Exclusive Locks

Exclusive Locks

The transaction that locked the object has the sole access rights to the object.

Shared Locks

When concurrent transactions are granted read access on the basis of a common lock.

Lock manager

has to know beforehand the type of lock before a lock can be granted.

READ_LOCK

to check the type of lock

WRITE_LOCK

issue the lock

UNLOCK

release the lock

Two-Phase Locking to Ensure Serializability

Define the way a transaction acquire and relinquish locks.

Growing Phase

the time the transaction acquires all the required locks without releasing any data. After acquiring all the locks, the transaction is now in its locked point.

Shrinking Phase

the time when the transaction releases all the locks and is no longer able to obtain any new lock.

Deadlock

occurs when two transactions wait indefinitely for each other to unlock data

Deadlock prevention

the DBMS aborts a transaction requesting a new lock if the possibility of deadlock can occur.

Deadlock detection

DBMS periodically check the database for deadlocks. If there is a deadlock, one of the transaction is aborted – the “victim.”

Deadlock avoidance

the transaction must obtain all of the lock it needs before it can start to execute. Requires that transactions obtain locks in succession.

Concurrency Control with Time Stamping Methods

This approach to scheduling assigns global, unique time stamp to each of the concurrent transactions.

Concurrency Control with Optimistic Methods

The assumption here is that majority of the transactions do not conflict.

Read phase

the transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database.

Validation Phase

It validates the transaction to ensure that changes do not affect the integrity and consistency of the database.

Write Phase

Changes to the database becomes permanent.

Uncommitted read

also known as dirty read. It is the lowest level of isolation, and provides the highest degree of concurrency.

Cursor stability

the default isolation level [up to DB2 9.5].

Currently committed

the new default behavior [ starting with DB2 9.7] for the cursor stability isolation level.

Read stability

All the rows an application retrieves within a unit of work are locked.

Repeatable read

It is the highest isolation level

Isolation level

is normally defined at the session or Application Level.

Lock Escalation

Occurs when the optimizer thinks that it is better to lock on the entire table rather than multiple row locks.

Locklist

The amount of memory (in 4k pages) reserved to manage locks for all connected applications.

Maxlocks

Maximum percentage of the entire lock list that a single application can use up.

Lock Wait

When two or more applications need to perform an operation on the same object, one of them may have to wait to obtain the needed lock.

Locktimeout

The time an application waits for a lock is controlled by the database configuration parameter

Current Lock Timeout

can be used to set the lock wait for a given connection.