Let’s have a more detailed look at both isolation levels. Read Committed Snapshot Isolation (RCSI).Since SQL Server 2005, the relational engine provides 2 new optimistic isolation levels that are based on the concept of row versioning as discussed in the previous section: Let’s have a look now at the 2 new optimistic isolation levels that SQL Server provides you. In addition you also have to plan and size TempDb accordingly, because you will introduce additional I/O, which can lead to contention problems in a default configuration. One side-effect of adding this pointer is that every record gets expanded by 14 additional bytes. The following picture illustrates this concept. Writers (UPDATE, DELETE statements) transparently copy old versions to the version store, and the new version points to the older version in TempDb. Older, previous versions are stored in the so-called Version Store, which is persisted in TempDb. Instead of waiting until a shared lock is acquired successfully, readers get back a previously committed version of the row. The idea behind row versioning is that readers (SELECT queries) don’t acquire shared locks anymore. Optimistic concurrency was introduced back with SQL Server 2005 and is based on the principles of Row Versioning. A few weeks ago I have already blogged about Pessimistic Concurrency that is the default concurrency model that SQL Server uses out of the box. Today I want to talk about Optimistic Concurrency in SQL Server.
0 kommentar(er)
