Re: sql server2005 for a change in 3TB range

Hi

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:

      * The READ COMMITTED isolation level with the
        READ_COMMITTED_SNAPSHOT database option set to ON. 
      * The SNAPSHOT isolation level. 

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.

Cheers

Peter

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>

> 


Other related posts: