Here is a good article from Omar AL Zabir
. If you are also using ASP.NET Membership/Role/Profile providers, or at least checking if you can use it as i do, it has pretty good and practical results. Thanks Omar for this informative and beneficial article.
Last year at Pageflakes, when we were getting millions of hits per day, we were having query timeout due to lock timeout and Transaction Deadlock errors. These locks were produced from aspnet_Users and aspnet_Membership tables. Since both of these tables are very high read (almost every request causes a read on these tables) and high write (every anonymous visit creates a row on aspnet_Users), there were just way too many locks created on these tables per second. SQL Counters showed thousands of locks per second being created. Moreover, we had queries that would select thousands of rows from these tables frequently and thus produced more locks for longer period, forcing other queries to timeout and thus throw errors on the website.
If you have read my last blog post, you know why such locks happen. Basically every table when it grows up to hold millions of records and becomes popular goes through this trouble. It’s just a part of scalability problem that is common to database. But we rarely take prevention about it in our early design.
The solution is simple, you should either have WITH (NOLOCK) or SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED before SELECT queries.Either of this will do. They tell SQL Server not to hold any lock on the table while it is reading the table. If some row is locked while the read is happening, it will just ignore that row. When you are reading a table thousand times per second, without these options, you are issuing lock on many places around the table thousand times per second. It not only makes read from table slower, but also so many lock prevents insert, update, delete from happening timely and thus queries timeout. If you have queries like “show the currently online users from last one hour based on LastActivityDate field”, that is going to issue such a wide lock that even other harmless select queries will timeout. And did I tell you that there’s no index on LastActivityDate on aspnet_Users table?