posted time Created time: 2017-02-09 Last updated time:

Feature of Serializable & Read Committed Isolation Level

Relational Database Management System has isolation levels. Alinous Elastic DB has 2 isolation levels, SERIALIZABLE and READ COMMITTED. They are different feature, and suitable usage.

Feature of Isolation Levels

The transaction of each isolation level executed different way in locking. Therefore their performance and way to keep consistency is different.

Transaction Isolation Level & Speed

READ COMMITTED

READ COMMITTED is the default isolation level, and it is best way to OLTP applications.

Main Transaction Phase

The main phase of a transaction starts from BEGIN SQL statement.

In this phase, UPDATE and DELETE SQL statements uses DML locks. The DML locks does not affects access by SELECT statement.

In addition to that SELECT Statement uses row and table locks. They have Update and Share modes. Usual SELECT statement uses Share Locks, and it with "FOR UPDATE" clause executes Update Locks.

COMMIT Phase

In COMMIT phase, INSERT SQL statement uses Unique Exclusive locks. This locks are to keep consistency of the primary keys. If the table schema has UNIQUE restrictions, they are used in UPDATE statements.

SERIALIZABLE

SERIALIZABLE isolation level is lock free transaction. Therefore if you use this for read only process, it works speedy.

Main Transaction Phase

Main transaction phase works speedy, because in this isolation level, it does not lock row and tables.

In addition to that, other transactions, which work in READ COMMITTED level, do not be affected.

COMMIT Phase

On commit phase, transaction locks DML lock, and checks the row to update. Then if the row is updated, the transaction fails.

COMMIT phase of SERIALIZABLE transactions are heavy process, because it has to check all of updated data, and has often fails.

Usage of Each Isolation Level

Those isolation levels are for OLTP and Batch execution.

READ COMMITTED for OLTP

READ COMMITTED level is suitable  for OLTP. It can keep data consistency by lock without failure. If failure occurs, the database application have to restart the transaction from the start.

In addition to that, the application have to write retry logic in it.

Even if locking cost exists, applications, which frequently update table records, can execute faster than execution with retry for failure.

SERIALIZABLE for Making Data for Data Analytics

SERIALIZABLE level is suitable for batch execution to fetch & transfer data to other database instances.

That is because it does not use lock, it can rapidly scan data with consistency, without affecting other transactions.



Go to Top