ACID explained

Acid: Atomicity, Consistency, Isolation, Durability

Isolation Level Explained:

Assuming that there is one read transaction (with two exact same read) and one write transaction, the time sequence are (read1,  write1 + commit write, read2 + commit read)

Serializable (Higest Level)-  Read and write transaction need to be fully serializable, i.e., One cannot start after the other one finished

Repeatable reads – No range lock support, this means it is possible for write transaction to delete a row and add another back. Even though the final result may (or maynot) be same for read (e.g, count(*), max() ), but it may not be expected.   The problem this causes is called Phantom Read.  For single row read, the DBMS must return the old value for the second SELECT.

Read commit – Each read is guaranteed to see “committed” write transaction value.  But two read is not guaranteed to get the same result. The problem this causes is called “non-repeatable read”

Read uncommitted – Read may get committed write result (dirty value). The problem this causes is called “Dirty read”

In summary, we have

Dirty Read            non-repeatable Read      Phantom Read

Read uncommitted             V                                   V                                        V

Read committed                 X                                    V                                        V

Repeatable read                 X                                    X                                        V

Serializable                         X                                    X                                        X

Advertisements
ACID explained

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s