For a long time now Oracle enthusiasts have bashed SQL Server for not having Optimistic Concurrency Controls. They would demonstrate a SQL Server reader blocking a writer and possibly a writer blocking a reader. Thus, having appeared to demonstrate that SQL can
This, of course, is a rather dirty trick; you only have to look at http://www.microsoft.com/sql/evaluation/compare/benchmarks.mspx to see SQL Server 2000 performance against various typical 3rd party LOB application workloads that involve reading and writing. So how did/do they perform these mischievous tricks - here
Open connection 1
SET StandardCost = 5.0,
ListPrice = 11.50
WHERE Name LIKE
Open connection 2
WHERE Name LIKE
You will now see that connection 2 will not return anything - instead it just sits there doing nothing. Here the writer on connection 1 blocks the reader on connection 2. This blocking will continue until connection 1 either commits or rollbacks the transaction it started with BEGIN TRANSACTION.
Well it would be very easy, at this stage, to get into a religious slanging match - and you know I don
At this point I
Okay - so you might have a
In SQL 2000 there are various ways to obviate the encumbrance of this
However as with Oracle, versioning takes up both processing and i/o at the server, resources that are potentially very expensive and not to be wasted if you don
I will argue that versioning is frequently used as a way to make developers jobs easier and not because it’s actually necessary. By that I also mean it makes it easier for developers to write sloppy code - and by that you can happily infer I believe it makes it easier to employ cheaper developers.
So how do we go about
employing these cheaper developers, sorry, reducing contention on the database? To do this we now have two new isolation levels:
1. SNAPSHOT ISOLATION (Transaction-level Snapshot)
Administrators must set the new ALLOW_SNAPSHOT_ISOLATION database option to allow Snapshot Isolation.
In order to start a transaction that uses Snapshot Isolation a developer must SET TRANSACTION ISOLATION LEVEL SNAPSHOT
RESULT: Every statement within a Snapshot Isolation Transaction sees the same version of data comprised only of committed changes which occurred before the start
of the transaction. Other statements inside other transactions do not see the changes made inside this Snapshot Isolation Transaction.
2. READ COMMITTED WITH SNAPSHOT ISOLATION (Statement-level Snapshot)
Administrators must set the new READ_COMMITTED_SNAPSHOT database option to allow Read Committed Snapshot Isolation
No further application level changes are required to have statements use Read-Committed Isolation.
RESULT: Each statement sees a version of the data that was committed just before the statement began, instead of when the resource is read. This is merely a new implementation of read committed that is non-locking and non-blocking; the data is accurate only as at the start of the statement.