Transactions Basics

Transactions Series Part 1

Transaction

It is a single logical operation on data.

ACID properties of Transaction

Atomic

The entire operation on the data should happen in its entirety, or should not happen at all. A transaction could involve multiple steps and for the transaction to complete, either all the steps should complete successfully or none at all. Any failure should abort the entire transaction.

Consistency

Data has data integrity or legal values which it can have. This data integrity should not be compromized by the transactions. For example, an account cannot have negative balance.

Isolation

The property which makes changes made by an operation local to it and not make other operations on system see it until its completion.

There are typically two ways of achieving isolation

• Lock based concurrency control
• Multiversion concurrency control

In the lock based concurrency model, typically objects are locked, so that another transaction will have to wait till the lock is released, which typically could be at the end of transaction.

In MVCC, multiple versions of the object are created for each transaction. For example, consider an object P, then this object has a read time called P-RT. Now, when a transaction Ti reads this object, a version of object P is created for the transaction and a time stamp for the transaction - Ti-TS is noted. Now, if the transaction Ti wants to write back any changes to P, then it is allowed to do only if another transaction Tj, whose time stamp Tj-TS is greater than Ti-TS and the object read time P-RT is less than Ti-TS. Otherwise the transaction is aborted and restarted.

By ANSI SQL standards, there are four levels of isolation -

• Serializable - This is the most strict form of isolation level possible. In this mode, the system makes all the transaction run serially. In the case of lock base concurrency control, the database system seems to acquire a range lock. This level guarentees that a select query always returns the same number of records with the same values when inside a transaction. With MVCC, a snapshot isolated view is created and shared for each accessing transaction. If the transaction updates, ofcoure, there is a potential for abortion with MVCC.
• Repeatable Read - This is the next level of isolation. With lock base concurrency control, all the data records or tuples are locked, but the range is not locked, and hence phantom reads are possible.
  Phantom Read - For example, If "select * from users where age between 10 and 30" selects 10 records, then these 10 records are locked and cannot be modified by another transaction. On the other hand, another transaction can write a new record with age 27, and if the first transaction ran the same query again, it would get another result with an extra tuple. This is because the range was locked. This is called phantom read. In this case, the system acquires a record lock on all the retrieved records but does not acquire a range lock allowing other transactions to insert records. This isolation level guarentees that a select query always returns the same value of the record.
• Read Committed - In this case, records retrieved by a query are allowed to be modifiable by other transactions. No locks are acquired at all. This is a Non-Repeatable-Read isolation level. Meaning that the value of a record may change between multiple select statement in a transaction; only if another transaction commits.
• Read Uncommitted - Same as Read Committed, the only difference being that a transaction can also see the dirty writes of another transaction. For example, a transaction can write data and before it can commit, the data is visible to other transactions. This is the lowest level of isolation.

Durability

This is the guarentee that once the user has committed the changes, the changes will persist and not be undone. It will survive any system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction.

Atomicity and Durability are guarenteed by either of two types of strategies -

• Write ahead logging (WAL)
• Shadow writing.

Write ahead logging - In a system using WAL, all modifications are written to a log before they are applied to the database. Usually both redo and undo information is stored in the log. The motivation for WAL is to allow updates of the database to be done in-place.

Shadow logging - Shadow paging is a copy-on-write technique for avoiding in-place updates of pages. Instead, when a page is to be modified, a shadow page is allocated. Since the shadow page has no references (from other pages on disk), it can be modified liberally, without concern for consistency constraints, etc. When the page is ready to become durable, all pages that referred to the original are updated to refer to the new replacement page instead. Because the page is "activated" only when it is ready, it is atomic. If the referring pages must also be updated via shadow paging, this procedure may recurse many times, becoming quite costly.