August 25, 2010 at 5:27 pm
From the link http://searchoracle.techtarget.com/tip/Oracle-vs-SQL-Server-Why-Oracle-wins
SQL Server has no multi-version consistency model, which means that "writers block readers and readers block writers" to ensure data integrity. In contrast, with Oracle, the rule is "readers don't block writers and writers don't block readers." This is possible without compromising data integrity because Oracle will dynamically re-create a read-consistent image for a reader of any requested data that has been changed but not yet committed. In other words, the reader will see the data as it was before the writer began changing it (until the writer commits). SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment.
Also, SQL Server will escalate row locks to page level locks when too many rows on a page are locked. This locks rows which are uninvolved in any updates for no good reason.
Is the above still true for SQL Server 2008?
Thanks
August 25, 2010 at 6:02 pm
Both are still true by default.
However, there are way to explicitly prevent them from happening.
SQL Server has no multi-version consistency model, which means that "writers block readers and readers block writers" to ensure data integrity.
snapshot isolation prevents this.
see http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx
•SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. Transactions that write data do not block snapshot transactions from reading data. You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.
SQL Server will escalate row locks to page level locks when too many rows on a page are locked. This locks rows which are uninvolved in any updates for no good reason
Table hints can prevent lock escalation.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply