Snapshot Isolation vs Serializable
The transaction isolation levels in DBMS helps in overcoming the concurrency issues like dirty read, phantom read, and non-repeatable read. Both snapshot and serializable isolation levels are considered the highest isolation levels as both of them avoid all the concurrency issues but in quite different ways.
“Snapshot” as the name suggests allows the transactions occurring concurrently to see the same snapshot or copy of the database as it was at the beginning of the transactions. Thus, allowing a second transaction to make changes to the data that was to be read by another concurrent transaction. This other transaction would not observe the changes made by the second transaction and would continue working on the previous snapshot of the database.
The transactions following a serializable execution though running concurrently appears as if they are running in a serial order. Serializable makes use of locks for reading and writing operations on transactions. A lock makes sure that no other concurrent transaction is allowed to amend the data used by the transaction holding the lock until it gets completed.
Difference between Snapshot isolation and Serializable:
|1.||In Snapshot, the SQL server avoids locks by making use of row versioning.||In Serializable, the SQL server makes use of locks and holds them until the transaction is committed or aborted.|
|2.||Follows optimistic concurrency control.||Follows pessimistic concurrency control.|
|3.||The concurrency level is high in comparison to serializable.||Low-level of concurrency is achieved as one transaction needs to wait for the completion of another transaction.|
|4.||Since no locks are imposed on data when it is read, other concurrent transactions are allowed to write data at the same time without any conflicts.||If two transactions try to read and write data at the same time then a deadlock occurs and one of the transactions is killed or rolled back as a deadlock victim.|
|5.||If two transactions try to update the same record at the same time then an update conflict occurs and the SQL server had to kill one of the transactions.||If two transactions try to update the same record then the second transaction will wait for the first transaction to either rollback or commit.|