Write to table with transaction without locking

lockingtransaction

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.

Best Answer

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.

Related Solutions

Sql-server – Can operations in a loop cause table locks

If you have wrapped the transaction block at the around of the insert command it would be only locking that record at a time, but if you have defined the whole loop in the transaction block, all involved records will be locked until the operation is finished.

Mysql – How to correctly implement optimistic locking in MySQL

Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.

Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.

In (C) issue:

CREATE TABLE test(
    id integer PRIMARY KEY,
    data varchar(255) not null,
    version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
BEGIN;
LOCK TABLES test WRITE;

In both (A) and (B) issue an UPDATE that tests and sets the row version, changing the winner text in each so you can see which session is which:

-- In (A):

BEGIN;
UPDATE test SET data = 'winnerA',
            version = version + 1
WHERE id = 1 AND version = 0;

-- in (B):

BEGIN;
UPDATE test SET data = 'winnerB',
            version = version + 1
WHERE id = 1 AND version = 0;

Now in (C), UNLOCK TABLES; to release the lock.

(A) and (B) will race for the row lock. One of them will win and get the lock. The other will block on the lock. The winner who got the lock will proceed to change the row. Assuming (A) is the winner, you can now see the changed row (still uncommitted so not visible to other transactions) with a SELECT * FROM test WHERE id = 1.

Now COMMIT in the winner session, say (A).

(B) will get the lock and proceed with the update. However, the version no longer matches, so it will change no rows, as reported by the row count result. Only one UPDATE had any effect, and the client application can clearly see which UPDATE succeeded and which failed. No further locking is necessary.

See session logs at pastebin here. I used mysql --prompt="A> " etc to make it easy to tell the difference between sessions. I copied and pasted the output interleaved in time sequence, so it's not totally raw output and it's possible I could've made errors copying and pasting it. Test it yourself to see.

If you had not added a row version field, then you would need to SELECT ... FOR UPDATE to be able to reliably ensure ordering.

If you think about it, a SELECT ... FOR UPDATE is completely redundant if you're immediately doing an UPDATE without re-using data from the SELECT, or if you're using row versioning. The UPDATE will take a lock anyway. If someone else updates the row between your read and subsequent write, your version won't match anymore so your update will fail. That's how optimistic locking works.

The purpose of SELECT ... FOR UPDATE is:

To manage lock ordering to avoid deadlocks; and
To extend the span of a row lock for when you want to read data from a row, change it in the application, and write a new row that's based on the original one without having to use SERIALIZABLE isolation or row versioning.

You do not need to use both optimistic locking (row versioning) and SELECT ... FOR UPDATE. Use one or the other.

Best Answer

Related Solutions

Sql-server – Can operations in a loop cause table locks

Mysql – How to correctly implement optimistic locking in MySQL

Related Question