After my last post about monitoring deadlocks, I will now show a way to prevent deadlocks and unnecessary locks and to improve system performance.
Introduction
SQL Server has a lot of working mechanisms. The lock mechanism is one of the most important mechanisms. Its goal is to keep and secure the data reliability and consistency. This mechanism is very complex and depends on many variables. The mechanism is designed to let the system work in an optimal way. It is based on the concept of “all or nothing at all”, which means either everything happens during a transaction or nothing happens at all. In specific cases, we would like to intervene by setting lock options manually and by doing so to affect the lock mechanism. It is not recommended to use those options on a daily basis, only in specific cases, when we know the type of data that we are dealing with (whether we are receiving data or want to make changes).
The option I am about to present is called “Readpast”. Readpast simply means that when we reach for a table data (select, update or delete) and one or more of the table rows or pages are locked by one or more transactions the lock mechanism will ignore those rows and retrieve the rest of the data which is not locked.
The advantage is obviously a performance improvement because there is no waiting time for another transaction's locks. But, the disadvantage is more significant than the advantage because even if we accept the loss of data we will not be able to know which part we lost and when.
The most common and recommendable way to use Readpast is when there is a need to implement a queue of the command list. For example, there is a table in which each row represents a command that needs to be executed. The table also has a separate and independent process that inserts new commands every time. The order of the commands is not important as long as they are being done. That is why Readpast is the ultimate solution because each process that will turn to the table will receive the next command with no waiting time.
Here is a simple example of how Readpast option impacts the lock mechanism:
Create a new table with two columns (command ID, command description).
Populate five new rows.
Transact-SQL
CREATE TABLE ReadPastTest ) CommandId INT, CommandDescription VARCHAR(MAX ( GO INSERT INTO ReadPastTest VALUES (1,'do a'),(2,'do b'),(3,'do c'),(4,'do d'),(5,'do e') GO SELECT CommandDescription FROM ReadPastTest GO
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE ReadPastTest
)
CommandId INT, CommandDescription VARCHAR(MAX
(
GO
INSERT INTO ReadPastTest
VALUES (1,'do a'),(2,'do b'),(3,'do c'),(4,'do d'),(5,'do e')
GO
SELECT CommandDescription
FROM ReadPastTest
GO
Select the rows.
Open a new transaction and update one of the rows and keep the transaction open.
Transact-SQL
BEGIN TRANSACTION UPDATE ReadPastTest SET CommandId = 11 WHERE CommandId = 1
1
2
3
4
BEGIN TRANSACTION
UPDATE ReadPastTest
SET CommandId = 11
WHERE CommandId = 1
3. Select the rows again with another query window that simulates a new process with the Readpast option.
Transact-SQL
SELECT CommandDescription (FROM ReadPastTest WITH (READPAST GO
1
2
3
SELECT CommandDescription
(FROM ReadPastTest WITH (READPAST
GO
4.Observe the changes.
5. Rollback the first transaction and select the table again.
Transact-SQL
ROLLBACK SELECT CommandDescription FROM ReadPastTest
1
2
3
ROLLBACK
SELECT CommandDescription
FROM ReadPastTest
As you can see at the first selection we get all five rows description and in the second selection, we get only four rows because one row is locked by the first transaction and it was skipped, at last, the missing row returns after the rollback.
In conclusion
the Readpast option is suited to specific cases only and can provide a perfect solution to those cases. It is important to beware not to use it on every deadlock or locks problems.