Introduction Some databases - SQL Server included - use locks to prevent users from seeing uncommitted modifications. In these systems, if UserA is modifying some set of data, UserB and all the rest of the users must wait until UserA is done modifying that data before UserB can get a shot at even reading the data, let alone modifying it. Databases place locks at all levels of their physical hierarchies: rows, pages (typically a few KB of rows), extents (typically a few pages), entire tables, and entire databases. Some databases (Oracle, others?) only use fine-grained row locks, others dont do row at all and only allow rough-grained page, extent, table, and database locks. Most databases - SQL Server included - support row locking, but often use rough-grained locks. This is because lock management is a royal pain. Locks arent small or simple entities, so if you only do row-level locking, you can get yourself into a world of pain: a million-row update can easily swamp memory and be a bear to manage. Databases that dont do just row-level locking often use a technique called lock escalation to achieve better performance. its clear from uncommitted outset that a whole table will be modified, these databases start off using row locks, and they make plans to trade these locks in for rough-grained locks later if too many rows are modified. Unfortunately, lock escalation introduces and amplifies a whole new problem: deadlock. If two users try to modify semantically-unrelated but physically-near data in two separate tables in reverse order, both will start off with row locks, then try to upgrade them to page locks, and the situation will be that each user wants something the other user has, so theyre stuck. This is deadlock. For example: * UserA modifies some rows in TableA, uncommitted a page lock affecting not just the rows UserA modified, but many others Somethings gotta give. To deal with this problem, the database occasionally looks for deadlocks, uncommitted kills off one of the transactions so uncommitted other can finish. It usually kills the one thats made the least modifications so that it minimizes the cost of rolling back changes. Databases that use only row-level locking almost never uncommitted this problem because two users rarely want modify the exact same row, and even more rarely do they attain locks in the perfectly poor order needed to cause databases like this use lock timeouts to prevent users from waiting too long for a lock. Query timeouts also factor in here. You can write code to retry queries that timeout, but this only automates database congestion. Any timeout that is often reached will only serve to worsen the user experience. Things simply should not take that long. In practice and under high load, SQL Servers locking system uncommitted is based on lock escalation - does not perform well. Why? Lock contention. Lock contention is the problems of deadlock and waiting for locks. In a system in which many users are modifying the database at once, and many more users are trying to access the database concurrently, the locks are flying, users spend a lot of time waiting to attain locks, deadlocks are frequent, and users are far from happy. Granted, if youve got a few occasional users you wont have much trouble with SQL out-of-the-box behavior. Youll be hard pressed to see these problems with simple in-the-office tests or deployments involving just a few users. But throw a couple hundred concurrent users at your database and a constant stream of INSERTS and UPDATES uncommitted quite a few DELETEs sprinkled in, and youll start reading Oracle literature and eyeing your war chest. However, Ive got uncommitted for you that will only cost you a code review, a few minor tweaks, and uncommitted test. You do a system test procedure in place, right? Lock Contention Solved If you used Streamload.com at all during June, July, and August, you probably got a You were the deadlock loser error, or a Lock timeout error, or an Object required error. These were all caused by lock contention. After scouring the documentation and talking to a few people, I learned what I summarized above and will say again here: * SQL Server starts with row-level locks, but often escalates these to page and table locks, causing deadlocks Fortunately, I stumbled across some obscure keywords from the SQL Server lexicon: NOLOCK and ROWLOCK. They are used like this: and NOLOCK and ROWLOCK NOLOCK Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk. ROWLOCK Using ROWLOCK politely asks SQL Server to only use row-level locks. You can use this in SELECT, UPDATE, and DELETE statements, but I only use it in UPDATE and DELETE statements. Youd think that an UPDATE in which you specify the primary key would always cause a row lock, uncommitted when SQL Server gets a batch with a bunch of these, and some of them happen to be in the same page (depending on this situation, this can be quite likely, e.g. updating all files in a folder, files which were created at pretty much the same time), youll see page locks, and bad things will happen. And if you dont specify a primary key for an UPDATE or DELETE, theres no reason the database wouldnt assume that a lot wont be affected, so it probably goes right to page locks, and bad things happen. By specifically requesting row-level locks, these problems are avoided. However, be aware that if you are wrong and lots of rows are affected, either the database will take the initiative and escalate to uncommitted locks, or youll have a whole army of row locks filling your servers memory and bogging down processing. One thing to be particularly aware of is the Management/Current Activity folder with Enterprise Manager. It takes a long time to load information about a lot of locks. The information is valuable, and this technique is very helpful, but dont be surprised if you see hundreds of locks in the Locks/Processes folder after employing this technique. Just be glad you dont have lock timeouts or deadlocks. Notes: Conclusion Results Big Disclaimer |
Ožujak 2008 (19)
Veljača 2008 (50)
Siječanj 2008 (67)
Prosinac 2007 (31)
Dnevnik.hr
Gol.hr
Zadovoljna.hr
Novaplus.hr
NovaTV.hr
DomaTV.hr
Mojamini.tv