What are Transactions ?
In an RDBMS, when several people access the same data or if a server dies in the middle of an update, there has to be a mechanism to protect the integrity of the data. Such a mechanism is called a Transaction. A transaction groups a set of database actions into a single instantaneous event. This event can either succeed or fail. i.e .either get the job done or fail.
The definition of a transaction can be provided by an Acronym called ‘ACID’.
(A)tomicity: If an action consists of multiple steps – it’s still considered as one operation.
(C) Consistency: The database exists in a valid and accurate operating state before and after a transaction.
(I) Isolation: Processes within one transaction are independent and cannot interfere with that in others.
(D) Durability: Changes affected by a transaction are permanent.
To enable transactions a mechanism called ‘Logging’ needs to be introduced. Logging involves a DBMS writing details on the tables, columns and results of a particular transaction, both before and after, onto a log file. This log file is used in the process of recovery. Now to protect a certain database resource (ex. a table) from being used and written onto simulatneously several techniques are used. One of them is ‘Locking’ another is to put a ‘time stamp’ onto an action. In the case of Locking, to complete an action, the DBMS would need to acquire locks on all resources needed to complete the action. The locks are released only when the transaction is completed.
Now if there were say a large numbers of tables involved in a particular action, say 50, all 50 tables would be locked till a transaction is completed.
To improve things a bit, there is another technique used called 2 Phase Locking or 2PL. In this method of locking, locks are acquired only when needed but are released only when the transaction is completed.
This is done to make sure that that altered data can be safely restored if the transaction fails for any reason.
This technique can also result in problems such as “deadlocks”.
In this case – 2 processes requiring the same resources lock each other up by preventing the other to complete an action. Options here are to abort one, or let the programmer handle it.
MySQL implements transactions by implementing the Berkeley DB libraries into its own code. So it’s the source version you’d want here for MySQL installation. Read the MySQL manual on implementing this.