Understanding Transaction Isolation Levels

Question

I am new to the topic, and I am trying to verify what I understand. so please consider the following example:-

Transaction contains a select and update statements, where update statement depends on the result set returned from the select statement. User A and B concurrently executes the transaction, Both users selected the data and about to execute Update. If User A executes the update first, User B will probably have a bug; because it has not up to date result set. This is called Phantom Read case.

For serializable isolation level: The above case will never happen. Transactions are completely isolated and can't work concurrently. Transaction of User B can't execute the select statement till User A completed its transaction. User B will Wait till transaction A complete.

For Repeatable read isolation level: Transaction B can read but cannot do modifications for data. Which can cause a Phantom Read.

Can you say whether this is a right understanding?

Answer 1

The default locking schemes in sql server will prevent queries from returning invalid data. When a command is issued it will wait for blocking commands or return a deadlock error after timeout. The victim is the easiest to rollback, usually reads. This prevents dirty data. You may want to look into lock hints and various ways to go about circumventing the default behavior.

Using NOLOCK for read-only commands will lead to better performance in a OLTP system when you do not care about what is going on while the command is issued.