Update without write lock

Question

Task:

Open first transaction (T1) that update some rows and rollback (always)

In same time (after open T1 but befor rollback) other transaction T2 can modify the same rows and commit it

In this case T2 wait T1 (used READ_COMMITTED_SNAPSHOT isolation level)

Can it be done without wait?

Example: First query window

IF NOT EXISTS ( SELECT  *
            FROM    sysobjects
            WHERE   name = 'TestLockTable'
                    AND xtype = 'U' ) 
BEGIN
    CREATE TABLE TestLockTable
        (
          Id INT NOT NULL ,
          Name VARCHAR(64) NOT NULL
        )
END

INSERT  INTO dbo.TestLockTable
    ( Id, Name )
VALUES  ( 1, 'test' )

BEGIN TRANSACTION T1

UPDATE  dbo.TestLockTable
SET     Name = 'test1'
WHERE   Id = 1;

Second:

BEGIN TRANSACTION T2

SELECT  *
FROM    dbo.TestLockTable
WHERE   Id = 1; --Select 'test' without wait

UPDATE  dbo.TestLockTable 
SET     Name = 'test2'
WHERE   Id = 1; --This T2 hang on

COMMIT TRANSACTION T2

And last:

ROLLBACK TRANSACTION T1

After rollback T1, T2 can commit and we get 'test2' in row

Answer 1

All DML takes X-locks, always. This is so that rollbacks can be performed reliably. No way around it, this is a core principle of the engine.

You need a different approach. Doing exotic stuff with locks is not recommended anyway because it is hard to get right and hard to test.

Without knowing what you are trying to accomplish I can't suggest a better alternative.

Answer 2

We can read a row with out wait using "with (nolock)" or "READ_COMMITTED_SNAPSHOT = false" but we cant update or delete a row with out an Exclusive lock..