9. Transactions

For local DBMS (Paradox, dbase and etc.) method of immediate mapping of modifications is typical. In such case it is impossible to refuse from data modification – as modifications are already physically made in DB. However deleted record can be manually reentered again. Or there is another possibility: deleted records can be saved in some temporal DB relation. If it is necessary to refuse from deletion of records, it is possible to transfer them from temporal storage back to the former relation.

Refusal from each data modification in one or several relations is called back-off to the last state of DB, where all kinds of data integrity are provided: referential integrity, conceptual integrity and corporate integrity limitations. In such way any modification of DB information must guarantee its transfer from one integral state to another.

Classic example of such DB transfer is an accounting transaction: sum S must be write off an account К and must be placed on the account D. Only successful execution of these two operations can guarantee integrity of information in the DB. But the integrity will be broken if in case of failure sum S will be written-off from account К, but will not be placed on the account D, or on the contrary, it will be placed on D, but not written-off from К. That is why in case of failure writing-off/placing of the sum that results of previous operation placing /writing-off must be cancelled. In other words, accounting transaction is composed from the group of the following elementary operations: writing-off of the sum S from account К; placing sum S to the account D.

There are mechanisms of DB modifications back-off in case of failure to comply terms of successful completion of all elementary operations that are included in the group. The base of these mechanisms is a transaction processing [2, 4, 6, 7]. Ordinary transaction processing is implemented in industrial DB. However several methods of application programs designing [5] permit to handle transactions for the tables of local DBMS also (Paradox, dbase). Moreover, both industrial and local, these methods include additional mechanism of control of DB modifications back-offs – that are called cached modifications (cached updates that can be interpreted as buffered modifications).

Transaction – is an operation or series of operations that are carried out by user or application program, that provide access to data or DB content modification [4].

Transaction is a logical unit of operations carried out by DB. It can be represented with separate program, be a part of program algorithm or even a separate instruction (for example INSERT or UPDATE) and include any quantity of operations that are carried out in DB. From the side of DB, execution of application program can be estimated as series of transactions with execution of data processing in the gaps between them.

Any transaction must always transfer DB from one consistent state to another, even though it is acceptable that coordination of its state will be broken during the transaction execution. That is why any transaction finishes with one of possible ways. In case of successful completion, transaction products are fixed (commit) in the DB and it jumps to the new coordinated state. If the transaction execution is not successful, it is cancelled (rollback). In this case former state must be reestablished in the DB. This process is called rollback of transaction. Fixed transaction can not be canceled. If it is found out that fixed transaction was mistaken, it will be necessary to carry out another transaction to cancel its operations. Such transaction is called compensating. It is necessary to mention that cancelled transaction can be reset once again later and depending on the reasons of previous rejection successfully completed and fixed in the DB.

No DBMS has inner possibility to determine what changes must be considered as a unit that generates one logical transaction. That is why there is a method that permits to determine bounds of each transaction from outside, from the side of user. In most data manipulation languages to determine bounds of separate transactions operators BEGIN TRANSACTION, COMMIT and ROLLBACK (or their equivalents) are used. If they were not implemented, all operations of the program that carried out modifications in the DB are considered to be united transaction. DBMS automatically carries out command COMMIT, if the program was successfully completed. Otherwise DBMS automatically generates command ROLLBACK.

There are four basic characteristics of transaction (ACID).

Atomicity. This characteristic states «everything or nothing». Any transaction is an indivisible unit of work that can be executed as a whole or not executed at all.

Conformity. Each transaction must transfer DB from one consistent state to another one.

Isolation. All transactions are carried out independently from each other. In other words, subproducts of incomplete transaction must not be available fro other transactions.

Duration. The products of successfully completed (fixed) transaction must be constantly saved in DB and must not be lost because of next failures.

9.3.1. Problems of simultaneous data modification

Problems of simultaneous data modification can arise in case of simultaneous work of several clients with the same relation. For example user А received data from relation Books and modified them after some time. At the same time, user В works with the same record of relation Books. He also modified data in the same record and tries to confirm these modifications. User С works with relation Books in reading mode only. At once several questions arise: can user В modify the record or not, if А has not confirmed his changes? Does С have right to read changes implemented by А and В? Can А see the modifications implemented by В and inversely?

To resolve mentioned problems there are several levels of transaction isolation Dirty Read, Read Committed, Repeatable Read. Level of transaction isolation determines:

if other (competing) transactions make modifications in data changed by current transaction;

if current transaction can see modifications made by competitive transactions and inversely.

9.3.2. Dirty Read transaction isolation level

Dirty Read transaction isolation level provides the possibility to competitive transactions to see modifications that are already made but not confirmed by current transaction. If current transaction will cancel modifications that were made, other transactions will see unauthentic data. This level of isolation can cause serious errors and is rarely applied.

9.3.3. Read committed transaction isolation level

Read Committed transaction isolation level provides the possibility to competitive transactions to operate affirmed changes that were made in current transaction only.

9.3.3.1. Data reading

For example transactions А and В are carried out by clients that correct or read data of the same DB relation. Transaction А changed data but did not confirm modifications. Competitive transaction В tries to read this data. Then it will get them in the state that was actual before the transaction А start-up. In other words transaction В doesn’t see modifications in relation that are already made by still not confirmed by transaction А.

There is also another possibility. Transaction А makes modifications in the data of some relation and doesn’t confirm them. At the same time transaction В of an application program starts. Relation where transaction А makes modifications is not opened. Then the attempt of transaction В initialization will be rejected. Client will be able to open data set only after confirmation of made modifications received from А transaction.

9.3.3.2. Data modification

For example transactions А and B are started by application programs. In each one there is an opened data set that refers to identic DB relations. Let’s consider that transaction А modified data but didn’t confirm modifications. Competitive transaction B made modifications of the same data also. In such case the attempt to confirm modifications from the side of transaction B will be rejected.

9.3.4. Repeatable Read transaction isolation level

Repeatable Read transaction isolation level creates the situation when current transaction always sees data in their form for the moment of start-up.

9.3.4.1. Data reading

Let’s consider that transaction А opened data set. Then transaction В made some modification in the same data and didn’t confirm them. In this situation, when the data set will be reopened again, transaction А will receive data in their form for the moment of start-up. But А will be able to see «her own» modifications. For example transaction В confirmed modifications that were made, and transaction А opened data set again. In this case transaction А will get data in their form for the moment of start-up.

9.3.4.2. Data modification

Let’s consider that transaction А made modifications in the data and didn’t confirm them. After that transaction В made modifications in the same data. Then the attempt of transaction В to confirm modifications that were made will be rejected.

9.3.5. Determination of transactions isolation level

Determination of transactions isolation level in the application program of the client depends on design environment. For example, in application programs written in environment Delphi or C-Bilder, transaction isolation level is determined by characteristics of the component Tdatabase – property Translsolation: Ttranslsolation. Possible values: tiDirtyRead, tiReadCommitted, tiRepeatableRead. Different DB servers can interpret different transaction isolation levels determined in characteristic Translsolation in different ways (table 9.1).

Table 9.1

InterВase manages transactions by means of SQL-operators SET TRANSACTION (to start-up transaction), COMMIT (to confirm transaction) and ROLLBACK, (to rollback the transaction). SET TRANSACTION has the following format [10, 14, 15]:

READ WRITE | READ ONLY determines data access level (on default READ WRITE);

WAIT | NO WAIT determines behavior of SQL-server when there is a conflict of record renewal between this transaction and another one, that made modifications in the same record earlier: WAIT (on default) makes present transaction to wait till the end of competitive transaction; NO WAIT determines emergency termination of the present transaction;

ISOLATION LEVEL determines transaction isolation levels on the server (on default SNAPSHOT);

RESERVING within the bounds of the present transaction provides exclusive access to the tables that are indicated in one or several lists. For the second case each element of the table has the following parameters:

PROTECTED READ – competitive transactions can read data but can not make any modifications;

PROTECTED WRITE – only transactions with levels SNAPSHOT or READ COMMITTED can read data and no competitive transaction can modify them.

ISO standard includes determination of transaction model built on application of two special operators - COMMIT і ROLLBACK. Most commercial implementations of SQL language, including InterBase, support this model that was first implemented in DBMS DB2 of IBM Company. In the standard it is mentioned that in SQL language transaction can be automatically started by any SQL-operator that is carried out by user or program (for example SELECT, INSERT or UPDATE). Modifications that are made in DB during the present transaction execution will not be available for other transactions that are carried out parallel, until this transaction will not be obviously finished. Transaction can be terminated in several ways.

1. Entry of operator COMМIT means successful completing of transaction. After its execution, modifications that were made in DB become constant. After operator COMМIT processing insertion of any operator automatically causes start up of new transaction.

2. Input of ROLLBACK operator means the rejection to finish transaction. As the result rollback of all modifications that are made within DB is carried out. After ROLLBACK operator processing the input of any operator automatically starts up a new transaction.

3. In case of applying SQL-operators in the text of the programm its successful completion automatically causes the termination of the last transaction that was started up before its completion even if COMМIT operator was evidently mentioned.

4. In case of applying SQL-operators in the text of the programm emergency termination of its work will cause failure of the last transaction that was carried out within this programm.

1. What problems assisted creation of DBMS transactions mechanism?
2. What is transaction?
3. Describe how does transaction mechanism provide data integrity?
4. What characteristics of transactions you know?
5. What transaction isolation levels you can name and comment?
6. What is the main disadvantage of Dirty Read transaction isolation level?
7. How does Read Committed transaction isolation level work in case of data reading and modification?
8. How does Repeatable Read transaction isolation level work in case of data reading and modification?
9. What capacities posesses SET TRANSACTION operator?
10. What methods of transaction completion you know?