Chapter 6: Optimizing Locking

 

In this scenario, you are a database developer in your organization. An order entry application contains various elements that list the database contents. During peak usage, the lists frequently take longer than usual to display, even though they don't contain a lot of data. You have noticed that these delays are caused by blocking. The order entry application also contains several reports that display aggregated sales information. When these reports are double-checked by auditors, they are sometimes found to contain inconsistent data. The values displayed at one place in a report might not be consistent with data in other places of the report. The application currently uses the default transaction isolation level and does not use locking hints. Design a solution for both problems by implementing the appropriate transaction isolation level.

Answers

 

• The blocked lists The problem with the lists in the application being blocked during peak hours is that they are being blocked by write transactions. Solving this problem can either be accomplished by using the read uncommitted transaction isolation level (that is, allowing dirty reads), or by implementing either the read committed snapshot or snapshot isolation level. Because there is no mention of dirty reads being allowed, you should assume that they are not allowed. The snapshot isolation level will cause row versions to be kept for more time than the read committed snapshot isolation level because only versions from the beginning of the transaction can be used. This will cause more space to be used in the tempdb system database; it will also cause more information to written to tempdb. This means that the read committed snapshot isolation level is the best solution to this problem. (If it can't be decided that dirty reads in the lists are acceptable, read uncommitted will provide the best performance.)

• The inconsistent reports The reports that contain inconsistent data are either experiencing non-repeatable or phantom reads. To solve this problem, the queries used in a report need to be combined in a transaction and use the repeatable read, serializable, or snapshot isolation level. Because the repeatable read isolation level doesn't protect against phantom reads, it will solve the problem only if the problem is non-repeatable reads. Both repeatable read and serializable drastically increase locked resouces. The reports are run against the same database as the order entry application; they should be avoided because they are highly likely to introduce blocking and deadlock problems. The snapshot isolation level will protect against both non-repeatable and phantom reads without introducing blocking or deadlock issues. Because the reports don't update data, update conflicts will not be a problem.