![]() > Keep your transactions as short and sweet as possible – don’t do a BEGIN TRAN, and then lump in a whole bunch of SELECT queries that grab configuration values from various tables Sorry if it sounds harsh, but sometimes I hate to hear advices like that. (Writing this “from db developer point of view”). Want the long version? We’ve got a whole ton of blocking resources to help you identify if you’ve got locked waits, which tables have the worst blocking problems, and find the lead blockers in blocking firestorms. Use the right isolation level – by default, SQL Server uses pessimistic concurrency (read committed), but there are better optimistic concurrency options like snapshot isolation and RCSI. ![]() Keep your transactions as short and sweet as possible – don’t do a BEGIN TRAN, and then lump in a whole bunch of SELECT queries that grab configuration values from various tables.Have enough nonclustered indexes to support your queries, but no more than that – typically, I recommend that you aim for 5 or less nonclustered indexes, with 5 or less fields each.How to Fix Locking, Blocking, and Deadlocks The developers just assume that their queries fail sometimes for no apparent reason, and it’s probably their own code, so they keep quiet. More often than not, deadlocks happen silently to service applications, and nobody even knows they’re happening – except the DBA. Users only notice blocking if it’s in a user-facing application that doesn’t automatically retry deadlock victims. SQL Server fixes this one for you by waking up every few seconds, looking around for deadlocks, and then killing the query that it believes will be the cheapest to roll back. (I’m keeping this one simple by referring to rows of the same table, but this can happen with lots of different objects scattered through different databases.) No query will automatically relinquish its lock – both of them are going to wait forever for the other query to finish because neither one is really aware of why it can’t get the lock it wants. Users hate blocking because it exhibits as very slow query performance.Īnd Tara takes out a lock on a different row,Īnd then Erik wants to add a lock on the row Tara already locked,Īnd then Tara wants to add a lock on Erik’s locked row,īoth queries want something that the other person has, and neither query can finish on its own. (Note that I’m not specifying the difference between readers and writers here – by default in the boxed product of SQL Server, readers can block writers, and writers can block readers.) SQL Server, sweet naive fella that it is, believes Erik’s query will eventually finish. SQL Server won’t kill or time out Tara’s query automatically because there’s no reason to. This is one of the many reasons why that application runs so well in development, but then falls over in production – there’s not enough concurrency to cause blocking.īlocking will solve itself whenever Erik’s query releases his locks. Tara’s query will wait forever for Erik’s query to finish. Until…Īnd shortly thereafter Tara wants that same row,īlocking is bad, but note that you won’t see blocking until you see concurrency. You don’t want to monitor for locking – it just happens, and that’s okay. If you’re the only person running a query, and you have a lot of work to do, you probably want to take out the largest locks possible (the entire table) in order to get your work done quickly. There’s nothing wrong with locking by itself.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |