Simple and proven techniques to quickly speed up your ASP.NET website
The reader can benefit from the previous articles on Pinpointing bottlenecks for better Database Access in ASP.Net and Fixing bottlenecks for better Database Access in ASP.Net.
In this section, you’ll see how to determine which queries are involved in excessive locking delays, and how to prevent those delays from happening.
You can find out which queries are involved in excessive locking delays by tracing the event “Blocked process report” in SQL Server Profiler.
This event fires when the lock wait time for a query exceeds the “blocked process threshold”. To set this threshold to, for example, 30 seconds, run the following lines in a query window in SSMS (locking.sql in the downloaded code bundle):
DBCC TRACEON(1222,-1)
Then, start the trace in Profiler:
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE mydatabase SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE mydatabase SET MULTI_USER;
(Move the mouse over the image to enlarge.)
When you click a Blocked process report event in Profiler, you’ll find information about the event in the lower pane, including the blocking query and the blocked query. You can get details about Deadlock graph events the same way.
To check the SQL Server error log for deadlock events:
Now that you identified the queries involved in locking delays, it’s time to reduce those delays. The most effective way to do this is to reduce the length of time locks are held as follows:
A second way to reduce lock wait times is to reduce the number of resources being locked:
select is_read_committed_snapshot_on from sys.databases where name='mydatabase'
To check whether row versioning is in use for a database, run:
SET LOCK_TIMEOUT 5000
Finally, you can set a lock timeout. For example, to abort statements that have been waiting for over five seconds (or 5000 milliseconds), issue the following command:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT Title FROM dbo.Book
UPDATE dbo.Book SET Author=’Charles Dickens’
WHERE Title=’Oliver Twist’
COMMIT
Use 1 to wait indefinitely. Use 0 to not wait at all.
Deadlock is a situation where two transactions are waiting for each other to release a lock. In a typical case, transaction 1 has a lock on resource A and is trying to get a lock on resource B, while transaction 2 has a lock on resource B and is trying to get a lock on resource A. Neither transaction can now move forward, as shown below:
One way to reduce deadlocks is to reduce lock delays in general, as shown in the last section. That reduces the time window in which deadlocks can occur.
A second way is suggested by the diagram—always lock resources in the same order. If, as shown in the diagram, you get transaction 2 to lock the resources in the same order as transaction 1 (first A, then B), then transaction 2 won’t lock resource B before it starts waiting for resource A. Hence, it doesn’t block transaction 1.
Finally, watch out for deadlocks caused by the use of HOLDLOCK or Repeatable Read or Serializable Read isolation levels. Take for example the following code:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT Title FROM dbo.Book WITH(UPDLOCK) UPDATE dbo.Book SET Author='Charles Dickens' WHERE Title='Oliver Twist' COMMIT
Imagine two transactions running this code at the same time. Both acquire a Select lock on the rows in the Book table when they execute the SELECT. They hold onto the lock because of the Repeatable Read isolation level. Now, both try to acquire an Update lock on a row in the Book table to execute the UPDATE. Each transaction is now blocked by the Select lock the other transaction is still holding.
To prevent this from happening, use the UPDLOCK hint on the SELECT statement. This causes the SELECT to acquire an Update lock, so that only one transaction can execute the SELECT. The transaction that did get the lock can then execute its UPDATE and free the locks, after which the other transaction comes through. The code is as follows:
SELECT b.Title, a.AuthorName
FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid
WHERE BookId=5
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…