Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: sql server2005 for a change in 3TB range

Re: sql server2005 for a change in 3TB range

From: Peter McLarty <>
Date: Tue, 13 Nov 2007 12:03:07 +1000
Message-Id: <>


Just catching up after a short hospital stay(not to serious)

Hrishy, from your comments here I am not sure you understand what the problem is and what grief it might cause you. Apologies if that is incorrect.
In Oracle we run a select and oracle places an non exclusive row lock, this in broader terms does nothing more than flag to other sessions that a user is querying that row.
If someone tries to do DDL on that table then this lock tells oracle to not allow it to proceed due to the current transaction. If someone else want to query that data they also put a lock. In the sense of locking these are not true locks as they don't block writers, if someone else comes along then and updates this row, when the original user tries something else then they may get an error about the data has been updated.
SQL Server by default locks a row on read so you see many select statement select a from table b nolock; Absurd IMHO but true. If you don't have nolock then your readers block other readers and writers and so your performance goes down.
Another common one you see is set transaction level read uncommitted, which on general terms means read uncommitted data. I have absolutely no idea as to why this is even allowed. If its not committed then I don't think it is a transaction and therefore should not be read nor should be able to be read except by a DBA doing some troubleshooting. In terms of stock availability or other financial terms I cannot see as to why it is allowed as part of the standard interface. I believe it is also used as a performance booster in SQL Server and maybe this is a heads up as to why. From MSDN

READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

In SQL Server 2005, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

I personally could not recommend nor use SQL server in a business environment where it was my choice to select a product. It wont stop me making a living supporting it for clients, but personally I don't consider it that good of a product due to what I consider a defective locking model.

Phil as to what you say about the snapshot yes as you use it at session level, without a clear model as to what people are doing and how and when you need to use it, I see it providing a lot of "bugs" in applications. Oracle is just so nice in that its default behaviour it takes this all away from the developer. You select and you add and records and you update and the engine, at the server level, looks after read writes,. locking and ACID transactions.



On Mon, 2007-11-12 at 11:52 +0000, hrishy wrote:

> Hi Phil
> Thank you for your inputs.
> My application is web based so i would be using
> connection pooling and the locking there would be
> different (wherein i would use a timestamp or
> something to see if a row is changed) and then do the
> dml.
> Do you think i should still be concerned about this
> difference in behaviour
> regards
> Hrishy

    <stuff snipped>


Received on Mon Nov 12 2007 - 20:03:07 CST

Original text of this message