TRANSACTION MANAGEMENT part1

TRANSACTION MANAGEMENT

Transaction

A transaction is a unit of a program execution that accesses and possibly modifies various data objects (tuples, relations).

A transaction is a Logical unit of database processing that includes one or more access operations (read -retrieval, write - insert or update, delete).

A transaction (set of operations) may be stand-alone specified in a high level language like SQL submitted interactively, or may be embedded within a program.

A transaction (collection of actions) makes transformations of system states while preserving the database consistency.

A user’s program may carry out many operations on the data retrieved from the database, but the DBMS is only concerned about what data is read/written from/to the database.

A transaction is the DBMS’s abstract view of a user program: a sequence of reads and writes.

PROPOERTIES OF TRANSACTION

The DBMS need to ensure the following properties of transactions:

1. Atomicity

– Transactions are either done or not done

– They are never left partially executed

An executing transaction completes in its entirety or it is aborted altogether.

–e.g., Transfer_Money (Amount, X, Y) means i) DEBIT (Amount, X);

ii) CREDIT (Amount, Y). Either both take place or none

2. Consistency

– Transactions should leave the database in a consistent state

If each Transaction is consistent, and the DB starts consistent, then the Database ends up consistent.

–If a transaction violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.

3. Isolation

– Transactions must behave as if they were executed in isolation.

An executing transaction cannot reveal its (incomplete) results before it commits.

–Consequently, the net effect is identical to executing all transactions, the one after the other in some serial order.

4. Durability

– Effects of completed transactions are resilient against failures

Once a transaction commits, the system must guarantee that the results of its operations will never be lost, in spite of subsequent failures.

SIMPLE MODEL OF A DATABASE

A database is a collection of named data items.

Granularity of data - a field, a record, or a whole disk block (Concepts are independent of granularity).

Basic operations are read and write:

read_item(X): Reads a database item named X into a program variable. To simplify our notation, we assume that the program variable is also named X.

write_item(X): Writes the value of program variable X into the database item named X.

READ AND WRITE OPERATIONS:

Basic unit of data transfer from the disk to the computer main memory is one block. In general, a data item (what is read or written) will be the field of some record in the database, although it may be a larger unit such as a record or even a whole block.

read_item(X) command includes the following steps:

1. Find the address of the disk block that contains item X.

2. Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).

3. Copy item X from the buffer to the program variable named X.

write_item(X) command includes the following steps:

1. Find the address of the disk block that contains item X.

2. Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).

3. Copy item X from the program variable named X into its correct location in the buffer.

4. Store the updated block from the buffer back to disk (either immediately or at some later point in time).

Two sample transactions

Transaction Example in MySQL

START TRANSACTION;

SELECT@A:=SUM(salary) FROMtable1 WHEREtype=1;

UPDATEtable2 SETsummary=@A WHEREtype=1;

COMMIT;

Transaction Example in Oracle(same with SQL Server)

•When you connect to the database with sqlplus(Oracle command-line utility that runs SQL and PL/SQL commands interactively or from a script) a transaction begins.

•Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction

TRANSACTION STATES

1. Active state

2. Partially committed state

3. Committed state

4. Failed state

5. Terminated State

State transition diagram illustrating the states for transaction execution:

Transaction Processing System

CONCURRENCY CONTROL

Concurrency in a DBMS

Concurrent execution of user programs is essential for good DBMS performance.

Because disk accesses are frequent, and relatively slow, it is important to keep the CPU humming by working on several user programs concurrently.

Users submit transactions, and can think of each transaction as executing by itself.

Concurrency is achieved by the DBMS, which interleaves actions (reads/writes of DB objects) of various transactions. Each transaction must leave the database in a consistent state if the DB is consistent when the transaction begins. DBMS will enforce some ICs, depending on the ICs declared in CREATE TABLE statements. Beyond this, the DBMS does not really understand the semantics of the data. (e.g., it does not understand how the interest on a bank account is computed).

Things get even more complicated if we have several DBMS programs (transactions) executed concurrently.

“Synchronization" of transactions; allowing concurrency (instead of insisting on a strict serial transaction execution, i.e., process complete T1, then T2, then T3 etc.)

- increase the throughput of the system,

- minimize response time for each transaction

Why do we need concurrent executions?

–It is essential for good DBMS performance! Disk accesses are frequent, and relatively slow.

Overlapping I/O with CPU activity increases throughput and response time.

What is the problem with concurrent transactions?

–Interleaving transactions might lead the system to an inconsistent state (like previous example):

Scenario: A Xact prints the monthly bank account statement for a user U (one bank transaction at-a-time).Before finalizing the report another Xact withdraws $X from user U. Result: Although the report contains an updated final balance, it shows nowhere the bank transaction that caused the decrease (unrepeatable read problem, explained next)

A DBMS guarantees that these problems will not arise.

–Users are given the impression that the transactions are executed sequentially, the one after the other.