Database Read-Consistency

It is important for a database to ensure that its users read committed data only in most of cases. Each database has its own way of ensuring the read-consistency.


(1) SQL Server
There is something called "Transaction isolation level" in SQL Server. The transaction isolation level is set to "Read Committed" by default. This default setting enforces that data readers read committed data only.

This default behavior could yield a drawback. If a user is updating records and the update transaction takes a long time to complete, other users won't be able to read the same records right away. The other users' read request will hang until the update transaction completes.

To reduce this type of blocking and waiting, SQL Server gives us another option of transaction isolation level: Read Committed Snapshot(RCSI). It uses tempdb to store the original versions of changed data. With this type of transaction isolation level, data readers can still read committed records while another user changes the same records.


(2) Oracle
There is something called, UNDO, in Oracle database. Undo information is a record of how to undo a change. This undo information is stored in a special tablespace,"UNDO tablespaces." Undo information provides a read-consistency to data readers even if the data that they read is currently being changed by another user (reading the data in the original state).

* A similarity can be observed between SQL Server's RCSI and Oracle's UNDO.

SQL Server Transaction - ACID Properties and Modes

A transaction is a logical unit of works in a database.


(1) A relational database transaction has ACID properties.

• Atomicity : All the changes in a transaction succeed or none of them succeed. There is no partially committed transaction. Either all changes committed or rolled back.
• Consistency: All transactions are performed in a way that the database can stay in a consistent state. 
• Isolation :  One transaction occurs in isolation from other transactions, and so the change made in the transaction is not visible to other transaction until it the transaction is committed.
• Durability : Databases ensure that all changes made by committed transactions are never lost by using logs that keep track of all the committed changes.

(2) Transaction Modes in SQL Servers

• Autocommit mode - a single DML and DDL statement is one transaction, and it will be automatically committed or rolled back  (default mode in SQL Server)
• Implicit transaction mode - SQL Sever automatically starts a transaction when we issue a DML or DDL statement. We must commit or roll back explicitly to end the transaction (default ANSI/ISO mode/default in Oracle)
• Explicit transaction mode - Begin a transaction with "BEGIN TRANSACTION" and end the transaction by commit or roll back.