Spring Server Locks Interview Questions and Answers
Explain the various types of concurrency problem. I.e. Lost or buried updates, uncommitted dependency, inconsistent analysis, phantom read.
Types of concurrency problems:
Lost updates: Occurs when 2 or more transactions select the same row/s and then update it on the basis of the original value. The last transaction update overwrites updates made by other transaction resulting in lost data.
Uncommitted dependency: Occurs when a second transaction selects row/s being updated by another transaction. The second transaction is reading the data that has not yet been committed and may be changed by the transaction executing updates on the row.
Inconsistent analysis: Occurs when a second transaction accesses row/s several times and reads different values every time. It involves multiple reads of the same row/s and every time the data is changed by another transaction.
Phantom reads: Occurs when an insert or delete statement is performed on a row/s that belongs to a resultset being read by another transaction.
What is Optimistic concurrency?
Optimistic concurrency: It assumes that resource conflicts between multiple users are very unlikely to occur and thus allows transactions to execute without any locking mechanisms on the resources. It is only while changing the data that a check is made on resources if any conflicts have occurred. If there’s a conflict then the application must read the data again and try to change it as well.
What are the differences between lost updates and uncommitted dependencies?
Last update overwrites other updates
Cannot report on data that does not exist.
Data is lost
Access a row being updated by others.
May report on data that does not exist
Updates are lost
What is Pessimistic concurrency?
Pessimistic concurrency: Assumes that resource conflicts between multiple users are very likely to occur and hence locks resources as they are used by transactions for the duration of the transaction. A transaction is assured of successful completion unless a Deadlock ocurrs.
Explain the isolation level that SQL Server support.
Isolation levels supported by SQL Server:
Read uncommitted: Lowest level of isolation
Read committed: Default
Serializable: Highest level of isolation. All transactions are isolated from each other completely.