READPAST: what is it good for?
After my last post about monitoring deadlocks I will now show a way to prevent deadlock and unnecessary locks and to improve system performance.
SQL Server has a lot of working mechanisms. The lock mechanism is one of the most important mechanism. Its goal is to keep and secure the data reliability and consistently. This mechanism is very complex and depends on many variables. The mechanism is designed to let the system work in the 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 bases, only on 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 transactions 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 command list. For example: there is a table which each row represents a command that needs to be executed. The table also has a separate and an 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.
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
CREATE TABLE ReadPastTest
CommandId INT, CommandDescription VARCHAR(MAX
INSERT INTO ReadPastTest
VALUES (1,'do a'),(2,'do b'),(3,'do c'),(4,'do d'),(5,'do e')
Select the rows.
Open a new transaction and update one of the rows and keep the transaction open.
BEGIN TRANSACTION UPDATE ReadPastTest SET CommandId = 11 WHERE CommandId = 1
SET CommandId = 11
WHERE CommandId = 1
Select the rows again with another query window that simulate a new process with the Readpast option.
SELECT CommandDescription (FROM ReadPastTest WITH (READPAST GO
(FROM ReadPastTest WITH (READPAST
Observe the changes.
Rollback the first transaction and select the table again.
ROLLBACK SELECT CommandDescription FROM ReadPastTest
As you can see at the first selection we get all five rows description and 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.
the Readpast option is suited to specific cases only, and can provides a perfect solution to those cases. It is important to beware not to use it on every deadlock or locks problems.