Consistent state in database refers to a condition whereby only valid data can be allowed to be written into the database. If a transaction is executed and the same breaches the rules of consistency, then the all transaction is rolled back and the database is taken back to a state that is consistent with the rules. Contrastingly, if a transaction executes and becomes successful, the database is taken from one state to a state that complies with the rules of consistency.
This concept of state consistency is essential in maintaining databases free from errors and other database anomalies that may occur as a result of incomplete transactions. This helps the database to be consistent in that there are no collisions experienced whenever data is retrieved or written to in two or more concurrent transactions.
Level of isolation is the degree at which a transaction is prepared to take in inconsistent data. This is the separation of transactions such that one transaction is isolated from one another. To increase the level of concurrency, the isolation level should be lowered but this pose great deal of incorrectness in data. Contrastingly, increasing the level of isolation enhances data correctness but the concurrency is affected a great deal. The level of isolation required by an application is determines the locking capabilities used by the SQL Servers. The levels of isolation in SQL92 are:
Read Uncommitted – this is the lowest level of transaction isolation and in this is whereby physically corrupt data remains unread.
Read Committed – this is the default level of isolation in the SQL Server.
Repeatable read
Serializable – this is the highest level of isolation whereby transactions are fully isolated.
In case transactions are executed in of serializable isolation level, concurrent overlapping transactions are assured of serialization. To prevent loss of updates in case of two transactions retrieving data from same row and finally update based on previously retrieved data, transactions should be executed at repeatable read isolation or higher level of isolation.
References
Kedar, S. (2009). Database Management System. Australia: Technical Publications.
Thomasian, A. (2010). Database Concurrency Control: Methods, Performance, and Analysis. Ney York: Springer.
