A guide to Understand Key wait resource in Blocking

During SQL blocking , creating a Blocked Process Report is very useful in reporting Blocking details. When you’re interpreting the Blocked Process Report – it is important to understand what resources are blocked. Reading through the Report details , the waitresource indicates the resource needed by the task. Let’s break down the details to understand the resource. KEY: db_id:hobt_id (index key hash value). KEY Identifies the key range within an index on which a lock is held or requested. In our example : KEY:9:330708921417728 process id="processb853d8" taskpriority="0" logused="0" waitresource="KEY:9:330708921417728 (03000d8f0ecc)" waittime="572343" ownerId="602608592" transactionname="SELECT" lasttranstarted="2012-12-14T09:46:57.550" XDES="0x267d67c8" lockMode="S" schedulerid="4" kpid="5688" status="suspended" spid="81"... Read more →

SQL Server – READUNCOMMITTED, NOLOCK and dirty reads

Question: A workflow application requires an unobtrusive lock on data in a transactional database. It’s a critical transactional system, so I don’t want to create blocking to INSERT or UPDATE statements by acquiring Shared Locks. What are the advantages\ disadvantages of using the READUNCOMMITED table hint? Answer: Using the READUNCOMMITED or NOLOCK table hint for applications should be used with caution. Analysis should be based on comparing the Concurrency levels required versus data access. Read more on SQL Server – Concurrency Control – Optimistic and Pessimistic Advantages 1) Allows dirty reads 2) A Shared Lock is not issued. This allows... Read more →

SQL Server – Concurrency Control – Optimistic and Pessimistic

Concurrency control manages conflicts in data modification. Optimistic Locking and Pessimistic Locking have different consequences for data concurrency levels. Optimistic locking assumes the relevant data won’t change while the application reads and Pessimistic locking assumes changes may occur and locking is applied. There a two categories of concurrency control 1) Pessimistic 2) Optimistic Pessimistic is normally used in high contention environments. An example, is a financial transaction system where there are many writes and reads on the same data. When USER A applies a lock , no other user can commit a conflicting action until USER A releases the lock.... Read more →

How to Use sys.dm_tran_locks to find requests waiting for an exclusive lock

 Use the DMV sys.dm_tran_locks to identify Exclusive Locks and attempts on obtaining an Exclusive Lock Read more →

How to find Open SQL Transactions

Help!! I need to find open sql transactions Read more →