Transactions are great. They keep your data together atomically, so you’re not in for any nasty surprises. But even a novice knows better than to leave transactions open, waiting for user interaction. If you do, lock waits and probably deadlocks will pile up in no time.
So how do you book a flight without blocking all the other users or losing your seat to somebody else while you make up your mind?
Isolation levels affect how aggressively SQL Server places and holds locks on tables and schemas. Get too lazy and you’ll end up with phantom data and dirty reads. Be too zealous, and you’ll end up troubleshooting deadlocks. Here’s an overview of the different types of isolation levels available, to help you choose which one is best for you.
Deadlocks are typically encountered in high-load scenarios or in complex concurrent transactions. And the worst part is, you probably won’t see them until you’re live in production. Luckily, finding and solving deadlock issues isn’t as complicated as it may sound. All it takes is a bit of logging and patience, along with some basic know-how of how locking mechanisms work in SQL Server.
sqlsunday.com 