Transactions are an integral part of any OLTP database system. They manage data consistency and data concurrency issues, to make sure that data always remains in a valid state in the database, when multiple sources read or update the same data at the same time. In SQL Server, this is achieved through a locking mechanism that SQL Server applies while reading and writing data from and to the database. The lock manager in SQL Server is responsible for applying this locking mechanism. SQL Server issues different types of locks on different types of resources, such as database, file, object, table, extent, page, and key.
While working with transactions, there is always a chance that you will face the issues caused by transactions. These issues are generally related to locking, blocking, and deadlocking. We often need to troubleshoot such issues and fix them, so that we can avoid them.
This chapter will discuss such challenges and provide you with insight on how to cope with these...