MySQL Transaction

A transaction is a unit of program execution that accesses and possibly updates various data items.

Another, A transaction is an execution of a user program and is seen by the DBMS as a series or list of actions i.e., the actions that can execute by a transaction includes the reading and writing of database.

Transaction Operations:

Access to the database is complete in a transaction by the following two operations,

1)read(X): Performs the reading operation of data item X from the database.
2)write(X): Performs the writing operation of data item X to the database.

Example:

Let T1 be a transaction that transfers $50 from account A to account B. This transaction can illustrate as follows,

T1 : read(A);
A := A –50;
write(A);
read(B);
B := B + 50;
write(B);

Transaction Concept:

  • The concept of transaction is the foundation for concurrent execution of transaction in a DBMS and recovery from system failure in a DBMS.
  • A user writes data access/updates programs in terms of the high-level query language supporte by the DBMS.
  • To understand how the DBMS handles such requests, with respect to concurrency control and recovery, it is convenient to regard an execution of a user program or transaction, as a series of reads and writes of database objects.
  • To read a database object, it is first brought in to main memory from disk and then its value is copy into a program. This is done by read operation.
  • To write a database object, in-memory, copy of the object is first modify and then written to disk. This is done by the write operation.

Properties of Transaction (ACID):

There are four important properties of transaction that a DBMS must ensure to maintain data in concurrent access of database and recovery from system failure in DBMS.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Atomicity :

Atomicity ensures that each transaction is treat as a single “unit”, which either success completely, or fails completely. If any of the statements making a transaction fails to complete, the entire transaction fails and the database is left unchange. An atomic system must ensure atomicity in each and every situation, including power failures, errors and crashes etc.

Consistency :

A transaction either creates a new and valid state of data, or if any failure occurs, returns all data .This prevents database corruption by an illegal transaction, but does not ensure that a transaction is correct.

Isolation:

Transactions are often execute concurrently (eg. reading and writing to many tables at the same time). Isolation guarantees that concurrent execution of transactions leaves the database in the same state.

1) Isolation is the main goal of concurrency control;
2) However, whether or not the transaction is allow to see even the most recent commit changes depends on the isolation level

Durability:

Durability guarantees that once a transaction has been committed, it will remain the same even in the case of a system failure (eg. power outage or crash) and system restart,the data is available in its correct state. This usually means that complete transactions are records in non-volatile memory.