How Deadlock occurs in SQL and how to handle it ?

A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.

When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.


The Deadlock

Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.

The Deadlock Situation
The below example shows the deadlock situation between the two transactions.

Transaction A


BEGIN TRANSACTION

UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

COMMIT TRANSACTION

Transaction B

BEGIN TRANSACTION

UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111

COMMIT TRANSACTION


If both the transactions are executed at the same time, then Transaction A locks and updates Customer table whereas transaction B locks and updates Orders table. After a delay of 5 ms, transaction A looks for the lock on Orders table which is already held by transaction B and transaction B looks for lock on Customer table which is held by transaction A. So both the transactions cannot proceed further, the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.


(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 52) was deadlocked on lock resources with 
another process and has been chosen as the deadlock victim.
Rerun the transaction.

But what if you don't like the default behavior (aborting the transaction)? Can you change it? Yes, you can, by rewriting Transactions A and B as shown below.

Transaction A


RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH

Transaction B


RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH

Here I have used Label RETRY at the beginning of both the transactions. The TRY/CATCH method is used to handle the exceptions in the transactions. If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock (Error_Number 1205), the transaction waits for 5 milliseconds. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. You can increase the delay according to the size of your transactions. After the delay, the transaction starts executing from the beginning (RETRY: Label RETRY at the beginning of the transaction) using the below statement:


GOTO RETRY -- Go to Label RETRY
This statement is used to transfer the control to the label named RETRY (which is at the beginning).

Now Execute the Transaction A and Transaction B at the same time. Both the transactions will execute successfully. Have a look into the outputs of the transaction where the exception occurred.

Hide   Copy Code
(1 row(s) affected)
Rollback Transaction

(1 row(s) affected)

(1 row(s) affected) 


Using RetryCounter

Now, I guess you understood how to handle deadlock without aborting the transaction. Let's move to the next interesting topic about deadlock. Imagine if there are more than two processes that read/update the Customer or Orders table at the same time. Below, I have modified both the transactions where I have shown how we can use RetryCounter to solve the problem.

Transaction A

DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if 
-- still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH

Transaction B


DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message 
-- if still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH

The RetryCounter variable used here gives a chance for the transaction to execute again if it fails due to deadlock (Error_Number 1205). In this example, the transaction can try to execute up to three times if it fails due to a deadlock. This scenario would be very useful if the transaction looking for the lock which was not released by the other transactions for a long time. So the transaction can try three times to check whether the required lock is available.

Troubleshooting High-CPU Utilization for SQL Server

The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. The Performance tab will show the high utilization as shown below: 

Next, we need to determine which process is responsible for the high CPU consumption. The Processes tab of the Task Manager will show this information:

For this types of situation we have to remember that CPU consume time in two modes as

1) Kernal Mode
2) User Mode

These two mode can be seen by "Performance Monitor" by monitoring "%Privilege Time" and "%User Time" counter.

Remember that "%Privileged time" is not based on 100%.It is based on number of processors.If you see 200 for sqlserver.exe and the system has 8 CPU then CPU consumed by sqlserver.exe is 200 out of 800 (only 25%).

If "% Privileged Time" value is more than 30% then it's generally caused by faulty drivers or anti-virus software. In such situations make sure the BIOS and filter drives are up to date and then try disabling the anti-virus software temporarily to see the change.

If "% User Time" is high then there is something consuming of SQL Server.
There are several known patterns which can be caused high CPU for processes running in SQL Server including

 Some of the most common causes for High CPU in SQL Server are

1 . Query executing causing CPU spike ( In general caused by optimizer picking bad plan)

2. High Compiles and Re-compiles ( In general stats change , schema change , temp tables , recompiled all the user defined SP's etc)

3. Running many traces.

Note: If your server is under severe stress and you are unable to login to SSMS, you can use another machine’s SSMS to login to the server through DAC – Dedicated Administrator Connection.


SELECT  scheduler_id
        ,cpu_id
        ,status
        ,runnable_tasks_count
        ,active_workers_count
        ,load_factor
        ,yield_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

See below for the BOL definitions for the above columns:

scheduler_id – ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 1048576. Those schedulers that have IDs greater than or equal to 1048576 are used internally by SQL Server, such as the dedicated administrator connection scheduler.
cpu_id – ID of the CPU with which this scheduler is associated.
status – Indicates the status of the scheduler.
runnable_tasks_count – Number of workers, with tasks assigned to them that are waiting to be scheduled on the runnable queue.
active_workers_count – Number of workers that are active. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended.
current_tasks_count - Number of current tasks that are associated with this scheduler.
load_factor – Internal value that indicates the perceived load on this scheduler.
yield_count – Internal value that is used to indicate progress on this scheduler.

The next step is to identify which queries are demanding a lot of CPU time. The below query is useful for this purpose (note, in its current form, it only shows the top 10 records).

SELECT TOP 10 st.text
               ,st.dbid
               ,st.objectid
               ,qs.total_worker_time
               ,qs.last_worker_time
               ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

This query has total_worker_time as the measure of CPU load and is in descending order of the total_worker_time to show the most expensive queries and their plans at the top:
Note the BOL definitions for the important columns:
total_worker_time - Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time - CPU time, in microseconds, that was consumed the last time the plan was executed.

I re-ran the same query again after a few seconds and was returned the below output.

Now figure out whether it is singe query or stored procedure causing CPU spike.

1. If the stats are up to date then estimated rows and estimated execution will be approximately same in the execution plan. If there is huge difference then stats are outdated and required update.

2. Rebuild or re-organize the indexes and also create if the indexes are not available.

3. If update statistics or rebuilding the indexes doesn't help you bringing down the CPU then tune the query one by one.

3. If the procedure is causing the CPU spike then

a. Use SET NOCOUNT ON to disable no of effected rows message. It is required only to test or debug the code.

b. Use schema name with the object name if multiple schemas exist in the database. This will helpful in directly finding the compiled plan instead of searching for the object in other schema. This process of searching schema for an object leads to COMPILE lock on SP and decreases the SP's performance. So always its better to refer the objects with the qualified name in the SP.

c. Do not use the prefix "sp_" in the stored procedure name . If you use then it will search in the master database. Searching in the master database causes extra over head and also there are changes to get wrong resulyt if the same SP found in the master database.

d. Use IF EXISTS (SELECT 1) instead of (SELECT * ) to check the existence of a record in another table. Hence EXIST will use True or False.

e. If the query which is spiking linked server query try changing the security of linked server to ensure liked server user has ddl_admin or dba/sysadmin on the remote server.

f. Try to avoid using the SQL Server cursors when ever possible and use while loop to process the records one by one.

g. Keep the transaction as short as possible - The length of transaction affects blocking and deadlocking.Exclusive lock is not released until the end of transaction. For faster execution and less blocking the transaction should be kept as short as possible.


h. Use Try-Catch for error handling it will help full to easily debug and fix  the issues in case of big portion of code.

2. If the system thread is consuming most of the CPU

If none of the SQL queries are consuming majority of the cpu then we can identify if the back ground threads is consuming the majority of CPU by looking at sysprocesses output for background threads.

Select * from sys.sysprocesses where spid<51


Check if you are hitting any of the known issues such as resource monitor may consume high CPU (hot fixes available ) or ghost clean up task uses 100% of the CPU on the idle system in SQL Server 2008 or SQL Server 2005.

SOL Server Login and Authentication and how it works ?

There are two types of authentication in SQL server as

1. Windows Authentication
2. SQL Authentication

Windows Authentication :

1. These are created for Active Directory User.
2. By the use of Active Directory user both windows and SQL server login are possible.
3. This option is selected at the time of installation.
4. It follow the windows password policy.

How It works :

When the user is connected by the Active Directory user, then windows verify the username and password and allocate a unique that is called TGT (Ticket Granting Ticket). Now when the user is going to login with the TGT, it will be allowed by the SQL server.

SQL Authentication :

1. For non active directory we create the SQL Authentication.
2. By default for every instance there is a user as "sa".
3. The information about SQL usernames and passwords are mentions in SQL server.

How it works :

At the time of login SQL server check and verify the give credentials.

SQL Server users creations :

Windows:

Create active directory user from windows Control Panel and add the user at the time of installation or use the following query as

use master

Go

Create Login <Login Name> from windows.

SQL:

In Object Explorer expand the security -> select login folder -> Add new login and follow the password policy.

NOTE :

Orphan Login:

When active user is deleted, the mapping login in SQL server is called Orphan Login.

To check the orphan login, use the following query as

sp_validatelogins

The solution of the problem is, create the active directory user.  

About DDL, DML, DCL, TCL, DQL in SQL Server

DDL- it is data definition language, this is used to define the database structure or schema (create, alter, drop, Rename, truncate and Comment.

• Create: it is used to create database and database Objects like tables, index, stored procedure, views, triggers, functions and etc.
  

Example: To create Customer table.

create table Customer(

Id int primary key identity(1,1) not null,

Name nvarchar(50) ,

HomeAddress nvarchar(50)

)

• Alter: it is used to create database and its Objects.
  
• Drop: it is used to delete objects from database.
  
• Truncate: it is used to remove all records from a table, including all spaces allocated for records are removed.
  
• Rename: It is used to rename the objects.
  
• Comment: // Single line Comments, /* --Multi Line Comments-- */ used to comment the SQL statements. CL in SQL
  

DML –it is Data Manipulation Language, this commands is used for managing data within schema objects (Select, Insert, Update and Delete).

• Select: To retrieve data from the database table.
  
• Insert: To insert date into a table.
  
• Update: To update the existing data in a table.
  
• Delete: delete all records from a table.
  

DCL-it is Data Control Language; this command is for control the access (Grant, Revoke) to data stored in the database.

• Grant: All users access privileges to database.
  
• Revoke: Withdraw user’s access privileges given by using the Grant command.
  

TCL-it is Transaction Control Language, this commands is used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. (Commit, Rollback and Savepoint).

• Commit: Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
  
• Rollback: Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
  
• Save point: creates points within groups of transactions in which to ROLLBACK.
  
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
  

DQL-it is Data Query Language, this commands is used to retrieve(select) data from the database .

• Select: To retrieve data from the database table.
  

What is SQL Server (Definition)

SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables. SQL server works on computer paging. Like all major RBDMS, SQL Server supports ANSI SQL, the standard SQL language

SQL Server History:

Code named Yukon in development, SQL Server 2005 was released in November 2005. The 2005 product is said to provide enhanced flexibility, scalability, reliability, and security to database applications, and to make them easier to create and deploy, thus reducing the complexity and tedium involved in database management. SQL Server 2005 also includes more administrative support.

The original SQL Server code was developed by Sybase; in the late 1980s, Microsoft, Sybase and Ashton-Tate collaborated to produce the first version of the product, SQL Server 4.2 for OS/2. Subsequently, both Sybase and Microsoft offered SQL Server products. Sybase has since renamed their product Adaptive Server Enterprise.