Another one close to my heart today, and apologies for it being a little shorter than usual, a problem with table locking is taking up all of my free time! This is just a short guide to what locking is, how it works, why each different type is good and bad, and in turn how it can create problems on your applications.
First I’ll start off by explaining how data is stored in a database, which will make sense of the page locking outlined below. Data in a table is stored in 8k blocks called pages. The amount of rows in these pages depends on the amount of data you are storing per row, data types etc. (Much longer story!). So if each page, for example, holds 8 rows of data, then if your application is updating/altering say 5 rows on that page, then for efficiency, by default, SQL server will grab the whole page, meaning that the 3 rows that aren’t being touched by this process, are still off limits to everyone else. Now onto the locking.
The purpose of locks are to maintain data integrity in a table. What I mean by this is that if application 1 tries to alter row 1, you want to make sure that application 2 doesn’t also alter row 1 at the same time and create inconsistent data (say the row now contains the amount from application 1, and the product id from application 2, giving you bad data). There areseveral types of locks on tables in SQL server. We have Row locking, Page locking, Extent locks, and give me the whole damn thing locks. The ones I want to focus on for this exercise are Row and Page locks (Lock escalation will occur regardless, but this might give you a fighting chance) Row locking locks each row being accessed, as it is accessed, and releases its lock once the operation has finished on the row. Page locking locks the data at a page level, meaning you can have several rows all locked at the same time, even if not all rows on the page are being used.
Now, when to use one over the other. As a general rule, in a transactional system, you will want to be using row locking. This is because you will always have a constant flow of activity going through the table, so if each transaction grabs chunks of the data for itself (page locking), the risk of transactions being dropped due to the page already being locked by other transactions, or daily processing, is quite high. Where as if row locking is set, the transaction will only take the row it needs, allowing your other transactions to continue to process on the table. (Disclaimer : Eventually if the operation is big enough and is running too inefficiently, SQL will just lock what it needs. But on Row by Row updates, this can make a big difference in locking/dead locking)
Page locking is useful for bulk operations, such as a large report, or a reconciliation process with a large amount of updates/inserts on the table. So a scenario may be you need to update your stock table for example once a day, it’s much quicker to just grab the table, perform all your updates, and release the table again, rather than trying to do row by row updates. There is nothing else trying to operate on this table at the same time, so it’s safe to lock everything for your nightly process.
That’s a lot of background for something you may or may not ever see as a Sys Admin, but if you do see problems related to it you’ll be happy you know about it! You are most likely to see problems in a transactional environment if page locking is enabled. Like I said above, it is because you have two or more applications trying to perform the same procedure, at the same time, but whoever gets there first gets the lock. If you see errors like “Database call failed” or “Could not call stored procedure X”, but other transactions are going through properly, this may be because page locking is enabled and some of your transactions are getting locked out. So the thing to do here, is to turn off page locking and have row locking enabled.
Microsoft break this down a lot better than I do here. While it is the table that is being locked, the lock level is actually controlled by the index on the table, which is why you want to alter the index. Most likely row locking and page locking are turned on, as this is the default setting for a new index. To turn page locking off you’ll want to run something like :
USE YourDBName;
ALTER INDEX Indexname ON YourDBName.YourTableName
SET (ALLOW_PAGE_LOCKS = OFF);
GO
Continue to monitor at this point and hopefully you will see your odd database call errors disappear. As always double check with your development team to make sure there isn’t a good reason page locking is involved, but in a transactional system 99% of the time you’ll want to have it set to row lock exclusively.
Sorry it’s short and sweet today but like I said, I’m getting killed by this exact problem at the moment! If you have any questions on anything written, please let me know via the comment field or on twitter @itch53. Also if anyone has anything in particular they’d like to see covered, or any questions specific to their problems, please don’t hesitate to ask.
Stephen