I am writing a wrapper for an MSSQL driver and I have been told we need to ensure that every incoming UPDATE or DELETE query contains the ROWLOCK table hint. It would be fantastic if I could do this at a transaction isolation level rather than some whackjob regex of every existing query to include WITH ROWLOCK. Now, I am either SOL or just very confused as to what it is that ROWLOCK actually does, so please let me know if my understanding is flawed.
I believe what I am trying to accomplish is ensure that any rows touched by an UPDATE or DELETE statement will not only remain consistent to the changes caused by my transaction and my transaction alone for its entire duration, but importantly prevent the affected rows from being touched by any other transaction concurrently.
The available isolation levels are (from https://msdn.microsoft.com/en-us/library/ms173763.aspx):
-- Syntax for SQL Server and Azure SQL Database
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
It seems as though both READ COMMITED and REPEATABLE READ are useful in preventing the data I am touching from being affected by any external changes in the scope of my transaction. However, if I am correctly interpreting the point of ROWLOCK, I don't just want to preserve the state of the data in my scope, I want to prevent anybody else from touching the data I'm dealing with in any form whatsoever until I am completely wrapped up and my transaction is committed.
I am convinced and hopeful that the problem here is misunderstanding what the point of ROWLOCK is. So let me restate, the task is to include the ROWLOCK table hint in every UPDATE or DELETE statement but do so from a transaction isolation level. I am using the Tedious Node.js driver, though I don't think that is relevant to this question.
If I understand your question clearly, it looks like you are looking for SNAPSHOT ISOLATION. It does not do row locks but use row versioning. As per your question, it looks like you do not want any locks on "Read" because of performance but wants to maintain the integrity for write operations.
The snapshot isolation level uses row versioning to provide transaction-level read consistency. Read operations acquire no page or row locks but does row versioning with DML statements.
As with (rowlock) is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete.
The above mentioned use of rowlock can be achieved using SNAPSHOT ISOLATION where you will be able to read unrelated rows.