LOCKS

1. What is Live Lock?

A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.

A human example of live lock would be two people who meet face-to-face in a corridor and each moves aside to let the other pass, but they end up moving from side to side without making any progress because they always move the same way at the same time and never cross each other. This is good example of live lock.

2. What is a WITH(NOLOCK)?

• WITH(NOLOCK) is used to unlock the data which is locked by the transaction that is not yet committed. This command is used before SELECT statement.
• When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction.
• Syntax: WITH(NOLOCK)
• Example:
  SELECT * FROM EmpDetails WITH(NOLOCK)
• WITH(NOLCOK) is similar as READ UNCOMMITTED

3. What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.

4. Explain a SQL Server deadlock, how a deadlock can be identified, how it is a performance problem and some techniques to correct deadlocks.?

• A deadlock is a situation where 2 spids have data locked and cannot release their lock until the opposing spid releases their lock. Depending on the severity of the deadlock, meaning the amount of data that is locked and the number of spids that are trying to access the same data, an entire chain of spids can have locks and cause a number of deadlocks, resulting in a performance issue.
• Deadlocks can be identified by Profiler in either textual, graphical or XML format.
• Deadlocks are a performance problem because they can prevent 2 or more processes from being able to process data.  A deadlock chain can occur and impact hundreds of spids based on the data access patterns, number of users, object dependencies, etc.
• Deadlocks could require a database design change, T-SQL coding change to access the objects in the same order, separating reporting and OLTP applications, including NOLOCK statements in SELECT queries that can accept dirty data, etc.

5. Explain Deadlock and Blocking?

Deadlocks and blocking are two common concurrency-related issues that can occur in database systems, including SQL Server.

1. Deadlock:

· Definition: A deadlock occurs when two or more transactions are waiting for resources (such as locks on rows, tables, or other database objects) held by each other, causing them to wait indefinitely. This situation creates a circular dependency where none of the transactions can proceed.

· Example: Transaction A holds a lock on Table X and needs a lock on Table Y, while Transaction B holds a lock on Table Y and needs a lock on Table X. Both transactions are waiting for each other to release the required locks, resulting in a deadlock.

·         Impact: Deadlocks can lead to transactions being terminated or rolled back by the database management system (DBMS), affecting application performance and data consistency.

2. Blocking:

· Definition: Blocking occurs when one transaction holds a lock on a resource and another transaction attempts to access the same resource but is forced to wait until the lock is released. The waiting transaction is said to be blocked by the transaction holding the lock.

· Example: Transaction A updates rows in Table X and holds exclusive locks on those rows. Meanwhile, Transaction B attempts to read or update the same rows in Table X but is blocked because it cannot acquire the necessary locks due to Transaction A's exclusive locks.

· Impact: Blocking can lead to decreased application performance, as blocked transactions may experience delays or timeouts waiting for locks to be released. It can also cause cascading effects if multiple transactions are blocked by each other.

 

6. How to find Deadlocks?

To find and diagnose deadlocks in SQL Server, you can use various methods and tools provided by SQL Server itself. Here are some common approaches:

1. SQL Server Management Studio (SSMS):

·   Use the Activity Monitor: In SSMS, go to the "Management" node, right-click on "Activity Monitor," and select "Active Deadlocks." This will show you the current deadlock graph and details.

·  Review the Error Log: SQL Server logs deadlock information in the error log. Look for messages with Event ID 1205, which indicate deadlock occurrences.

2. SQL Server Profiler:

·  Use SQL Server Profiler to capture deadlock events. Create a trace that includes the "Deadlock Graph" event or use the "TSQL_Locks" template, which includes deadlock events by default.

3. Extended Events:

·   Use Extended Events to capture deadlock information. Create an Extended Events session with events like "xml_deadlock_report" to collect deadlock graphs and related details.

4. System Health Session:

·   SQL Server maintains a System Health Extended Events session by default, which includes deadlock information. Query the system health session to retrieve deadlock events and details.

5. Querying DMVs:

· Use Dynamic Management Views (DMVs) such as sys.dm_exec_requests and sys.dm_tran_locks to monitor active transactions and locks, which can provide insights into potential deadlock situations.

6. Deadlock Graphs:

· When a deadlock occurs, SQL Server generates a deadlock graph that illustrates the dependencies and conflicts between the involved transactions. You can retrieve deadlock graphs from various sources mentioned above or by querying the system_health Extended Events session.

 7. how to find the root block in sql sever?

select * from sysprocesses where blocked<>0 and blocked not in (select spid from sysprocesses where blocked<>0)

8. Can u tell us something about how to find which sql table is having the lock and of what type. also please tell us how to remove a lock from a locked table?

In SQL Server 2000 (Enterprise Manager)

1. Expand server – management-currentActivity-expand
locks/processid and you will be able to see all the locks related information.

2.Expand server – management-currentActivity-expand Locks/object you can see locks by object information.

In SQL Server 2005 (SSMS, object Explorer)
Expand-server-management-double click Activity Monitor.
on left side you have three options to choose from, select those options and you can see all the locks related information.

run this stored procedure in the database.

1. sp_lock

to know the running process in the sql server, run this query,

2. select * from sysprocesses ( in sql server 2000)
3. select * from sys.sysprocesses ( in sql server 2005)

4. sp_who
5. sp_who2 will also give you some good information.

To work around the locks, you can run profiler to check which query is is creating a lock and if that is necessary.

Types of locks on object level, ( general idea)

Database : Database.
Extent : Contiguous group of eight data pages or index pages.
Key: Row lock within an index.
Page: 8-kilobyte (KB) data page or index page.
RID :Row ID. Used to lock a single row within a table.
Table: Entire table, including all data and indexes.

Types of locks;
Shared (S) – more than one Query can access the object.
Exclusive lock (X) – only one Query can access the object.
Update lock (U)
Intent share (IS)
Intent Exclusive (IX)

Just to give you a brief idea about locks, We have something called as transaction levels in sql server databases.

TRANSACTION ISOLATION LEVEL
level 0. READ COMMITTED
level 1. READ UNCOMMITTED
level 2. REPEATABLE READ
level 3. SERIALIZABLE

level 0 is the lowest level isloation level, if your database is set in this isolation level, no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This data will also read uncommitted data. Data which you have not comitted, you can still read that data.

level1 is the default isolation level of the database.
Under this category you will not be able to read uncomitted data, this is also called as dirty data. Under this we will have shared locks.

As the level increases the locks also increases. The highest is the serializable.

To make you understand in detail, lets see an example of what is committed data and what is uncomitted data.

use pubs
create table example1 ( eid int, ename varchar(10))

begin tran T1
insert into example1 values ( 1, ‘example’)
go

select * from example1 — this is uncomitted data.

The above is uncomitted transaction, because you started the transaction with a begin, you have to commit the transaction, untill then the transaction will not be uncommitted.

to commit the same transaction

commit tran T1

select * from example1 — this is committed data.

To check what is the current isolation level of your database, run this command,

Dbcc useroptions — check for isolation level.

If you dont want your query to put locks on objects you might want to use something like this,

select * from example1_1 with (nolock)

This will not keep any lock, not even a shared lock on the table.

9. Explain different types of Locks in SQL Server.

There are 3 kinds of locks in SQL Server
i.) Shared locks - they are used for operations which do not allow any change or update of data. For e.g. SELECT. 
ii.) Update locks - they are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes. 
iii.) Exclusive locks - they are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE.