Post reply

TRANSACTION ISOLATION LEVEL

  • December 12, 2003 at 2:20 pm

    #160473

    I see the default behaviour of sql server is to lock rows being updated even for select queries (READ COMMITTED).

    Is there any option that makes sql server behave like oracle, where selects will never be affected by locks reading previously comitted data?

    READ UNCOMMITTED will make the select to read uncommited data (dirty reads).

    Thanks

    Oscar

  • December 12, 2003 at 3:28 pm

    #486044

    You can change your SELECT statement(s) to use the WITH (NOLOCK) hint as in:

    SELECTs.SourceName,

    FROMtablea s with (nolock)

    WHEREs.ID = 'test'

    Francis

  • December 15, 2003 at 1:18 am

    #486104

    Oracle locking works in a different way to SQL Server - during an update if you do a read the data is not locked and you will see the original data. If you change SQL Server to use the WITH(NOLOCK) then you will see the uncommited data (dirty reads). I do not think there is any way of making SQL Server locking behave in exactly the same way as Oracle.

  • December 17, 2003 at 2:54 pm

    #486854

    The next version of SQL Server will allow you to do that with a new isolation level called SNAPSHOT. As I can see in BOL (Yukon), this will make SQL Server behaves like Oracle (with its Rollback segments).

    Carl

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply