If our results must always be based on committed data, we need to be willing to wait for changes to be committed. In earlier versions of SQL Server, the tradeoff in concurrency solutions is that we can avoid having writers block readers if we are willing to risk inconsistent data-that is, if we use Read Committed isolation. The space in tempdb used to store previous versions of changed rows is called the version store. It is possible to avoid blocking because as soon as one of the new database options is enabled, SQL Server starts using tempdb to store copies (versions) of all rows that have changed, and it keeps those copies as long as there are any transactions that might need to access them. Conversely, a process requesting a shared lock does not block when the requested resource currently has an exclusive lock. Or, using locking terminology, a process requesting an exclusive lock does not block when the requested resource currently has a shared lock. The difference between optimistic and pessimistic concurrency is that with optimistic concurrency, writers and readers do not block each other. Exclusive locks can be acquired when you use optimistic concurrency, so you still need to be aware of all issues related to lock modes, lock resources, and lock duration, as well as the resources required to keep track of and manage locks. Starting in SQL Server 2005, optimistic concurrency is available after you enable one or both of the database properties called READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION. Optimistic concurrency uses a new technology called row versioning to guarantee your transactions. Pessimistic concurrency uses locking to guarantee the appropriate transactional behavior and avoid problems such as dirty reads, according to the isolation level you are using. At the beginning of this chapter, I described two concurrency models that SQL Server can use.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |