All multi-user databases experience locks, blocking, and deadlocks. Learn what these mean and how to address them.
- [Instructor] In this video, we'll focus on performance impacts on your SQL instance by discussing what locks, blocking and deadlocks are and how they impact users of your database. Locks are held on SQL server resources such as rows. They occur during reads, insertions, updates and deletes and they prevent concurrent use by different transactions. They're designed to prevent "dirty reads." Now essentially what we mean by this, consider a bank account balance where there may be the potential of a deposit and a withdrawal or a balance transaction and a withdrawal taking place simultaneously.
A dirty read would be an example of somebody checking the balance and finding there to be 100 dollars in the bank account while at the same time somebody was making a withdrawal of 75 dollars in which case the actual balance would only 25 and if you were attempting to do a withdrawal of another amount such as 50 dollars, there would be conflict on the system. So therefore when you're doing the reads, inserts, updates and deletes, the rows are typically locked and the transactions that are taking place must wait for the current transaction to finish.
Minimizing the locks will increase your database performance according to how the users see things in terms of the performance of the database. Blocking is another type of lock which occurs when one lock causes another process to wait in a holding queue. The current process must end before the new one can begin its processing. So basically in a normal server environment, infrequent blocking locks are acceptable but if the blocking locks become common, or become more widespread, then there is probably some kind of a design or query implementation problem and you should do a design check of the database indexes or queries.
Finally we take a look at deadlocks. Now a deadlock is essentially an instance where two processes are each waiting for a resource that is held by the other process. So therefore process number one and process number two may each be waiting for the same resource but there's a lock held by the other resource so we have a deadlock situation. In this example to solve a deadlock, we see process one and process two, both in a deadlock on the SQL server. The lock manager's thread will check for deadlocks on the system and when its detection algorithm detects a deadlock, it will choose one of the processes using the server's process ID and it will designate that as a victim.
The lock manager will then initiate and send a 1205 error message to the client it has chosen as the victim and then it will kill that process ID. As a result, the deadlock is broken and now the remaining process has the ability to gain access to the service or to the instance that it was attempting to access. To monitor the performance on your database and to evaluate locks blocking in deadlocks, you can use the SQL server profiler. We'll cover the SQL server profiler in a later video.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options