Write to table with transaction without locking

Question

Is it possible to write to a database table using a transaction without locking it?

I want to run an update query on single field in a single record, but just in case there is an error in my application I want to be able to roll it back. The application could take a second or two to complete the entire process, so I'd like to prevent it from locking the table, as it's a heavily read table. From the application's perspective the field is insignificant, so I don't care if there are dirty reads on it.

(I'm aware of NOLOCK and READPAST for SELECT statments, but I'm not looking for that as a solution.)

Any help is appreciated.

Answer 1

Is it possible to write to a database table using a transaction without locking it?

Depends on what you mean by "it"? locks can be at different resource level meaning: row, page, table, non clustered index pages etc.

If you mean all of the above than the answer is no. At the same time you can avoid locks on some of the resources depending on the scope of your transaction and how many records you are updating.

I suggest you read this article :

Why do we need UPDATE Locks in SQL Server? by Klaus Aschenbrenner

Now let's look at an example using adventureworks database. To simulate what I am doing you can download this database from here.

 USE [AdventureWorks]
 GO

--Making sure we are only updating one row
SELECT COUNT(0) FROM [Production].[Product]
WHERE ProductId=318

BEGIN TRAN
UPDATE [Production].[Product] 
    SET [Name]='ML Crankarm_modified' 
    WHERE ProductId=318
--ROLLBACK TRAN

Since we are using BEGIN TRAN (explicit transaction) we can check what locks this session is holding.

SELECT 
  resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE request_session_id=59

Looking at these locks and lock compatibility chart you should be able to figure out what select statements you can run and which one will get blocked. Keep in mind it will also be dictated by your transaction isolation level.

For example you have Intent Exclusive lock on object id 482100758 which is production.product table itself and this lock is not compatible with Shared lock. Therefore you cannot select all records from this table. But if you want to select one or more records that does not have locks incompatible with Shared lock you should be able to select data.

Answer 2

I am not a SQL-Server expert. But in short "No", there is not possible to write into the database without locking. If you need a rollback plan i would go for something like a trigger, that will copy the data you want to modified in other table, so in case you need to rollback just need to update that data back. Of course you need to be very carefull of any foreign keys that can be modified at the same, adding then in your rollback plan. And race-conditions, that is other updates that mofified you record after.