SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, 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.

Fix/Workaround/Solution:
Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.

To reduce the chance of a deadlock:

• Minimize the size of transaction and transaction times.
• Always access server objects in the same order each time in application.
• Avoid cursors, while loops, or process which requires user input while it is running.
• Reduce lock time in application.
• Use query hints to prevent locking if possible (NoLock, RowLock)
• Select deadlock victim by using SET DEADLOCK_PRIORITY.

SQL SERVER 2005 has new priority HIGH as well as numeric-priority.

SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO

SQL SERVER – Trace Flags – DBCC TRACEON 

Trace flags are valuable tools as they allow DBA to enable or disable a database function temporarily. Once a trace flag is turned on, it remains on until either manually turned off or SQL Server restarted. Only users in the sysadmin fixed server role can turn on trace flags.

If you want to enable/disable Detailed Deadlock Information (1205), use Query Analyzer and DBCC TRACEON to turn it on.

1205 trace flag sends detailed information about the deadlock to the error log.

Enable Trace at current connection level:

DBCC TRACEON(1205)

Disable Trace:

DBCC TRACEOFF(1205)

Enable Multiple Trace at same time separating each trace with a comma.

DBCC TRACEON(1205,2528)

Disable Multiple Trace at same time separating each trace with a comma.

DBCC TRACEOFF(1205,2528)

To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.

DBCC TRACEON (1205, -1)

To enumerate a complete list of traces that are on run following command in query analyzer.

DBCC TRACESTATUS(-1)
 

Avoid deadlocking on your SQL Server

Deadlock:

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, 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.


Some tips for reducing the deadlock:

• Ensure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don't allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
• Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections.

Example:
You are a database developer for a clothing retailer. The company has a database named Sales. This
database contains a table named Inventory. The Inventory table contains the list of items for sale and the
quantity available for each of those items. When sales information is inserted into the database, this table
is updated. The stored procedure that updates the Inventory table is shown in the exhibit.
CREATE PROCEDURE UpdateInventory @IntID int
AS
BEGIN
DECLARE @Count int
BEGIN TRAN
SELECT @Count = Available
FROM Inventory WITH (HOLDLOCK)
WHERE InventoryID = @IntID
IF (@Count > 0)
UPDATE Inventory SET Available = @Count – 1
WHERE InventoryID = @IntID
COMMIT TRAN
END
When this procedure executes, the database server occasionally returns the following error message:
Transaction (Process ID 53) was deadlocked on {lock} resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
You need to prevent the error message from occurring while maintaining data integrity.
Change the table hint to UPDLOCK.
Explanation: This is a deadlock problem. We must resolve this problem. The SQL batch of this scenario
basically consists of an SELECT statement and an UPDATE statement. The SELECT statement includes a table
hint: WITH (HOLDLOCK).
This table hint is very restrictive. The rows are completely locked. We need to remove this restrictive table hint
and replace it with the table hint UPDLOCK, which is less restrictive than HOLDLOCK. It allows reads of the
rows, but no updates.
Note: table hint
A table hint specifies that a table scan, or one or more indexes, must be used by the query optimizer, or a
locking method must be used by the query optimizer with this table and for this SELECT. The query optimizer
can usually pick the best optimization method without hints being specified, therefore it is recommended that
hints only be used as a last resort by experienced developers and database administrators.

Reference Links:

http://www.sql-server-performance.com/tips/deadlocks_p1.aspx

Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

SQL SERVER – 2005 Locking Hints and Examples

Locking Hints and Examples are as follows. The usage of them is same same but effect is different.

ROWLOCK

Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000

UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'

Ranking Functions within SQL Server 2005

With SQL Server 2005, Microsoft introduced a number of new features. These new features make it easier for a DBA or SQL Server developer to code against and to maintain SQL Server databases. In this article I will discuss the new ranking functions provided with SQL Server 2005. These new functions make it much easier for you to code your T-SQL to associate a ranking to your result set. As I discuss each of the new ranking functions I will provide one or more examples to help illustrate how these new functions work.

What are Ranking Functions

Ranking functions are functions that allow you to sequentially number your result set. These functions can be used to provide you with a number of different sequential numbering schemes. For example you can number each row in your result set sequentially where the first row has a ranking number of 1, the second row has a ranking of 2, third row has 3, and so on. You can also use these ranking functions to sequentual number groups, so each group would have a numbering scheme of 1,2, 3, and then the next group would start over with 1, 2, 3, etc..

Test Data for My Examples

In order to provide examples of each ranking function I need to have some test data that the ranking function will process against. For my test data I will use a simple “Person” table. This table will consist of three columns “FirstName”, “Age” and “Gender”. Below is the code to create and populate my sample test data file.

SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')

ROW_NUMBER Function

The first ranking function I will discuss is the ROW_NUMBER function. This function returns a sequential number starting at 1 for each row or grouping within your result set. The ROW_NUMBER function has the following calling syntax:

ROW_NUMBER ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Where the:
“<partition_by_clause>” is a column or set of columns used to determine the grouping in which the ROW_NUMBER function applies sequential numbering.
<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).
To demonstrate how to use the ROW_NUMBER function, my first example below will sequentially number all the rows in my Person table, and order them by Age.

SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age], 
       FirstName,
       Age 
  FROM Person

Here is the result set for the above T-SQL code:

Row Number by Age    FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
3                    George     6
4                    Mary       11
5                    Sherry     11
6                    Sam        17
7                    Ted        23
8                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

Here you can see I have sequentially numbered all my Person table rows starting from 1, and the result set is ordered by the Age column. This ordering was accomplished by placing the “ORDER BY Age” criteria in the ORDER BY clause of the ROW_NUMBER function.
Suppose you do not want your result set to be ordered, but you want to simply just sequentially number each row. The ROW_NUMBER function requires an ORDER BY clause, so something is needed in this clause. In the following query I specified “SELECT 1” for the ORDER BY clauses, doing this just returned my rows physically how they where stored, and sequentially numbered them starting from 1:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set], 
       FirstName, 
       Age 
  FROM Person

Here is the result set from running the above query:

Row Number by Record Set FirstName  Age
------------------------ ---------- -----------
1                        Ted        23
2                        John       40
3                        George     6
4                        Mary       11
5                        Sam        17
6                        Doris      6
7                        Frank      38
8                        Larry      5
9                        Sue        29
10                       Sherry     11
11                       Marty      23

The ROW_NUMBER function not only allows you to order the entire row set, but you can also use the PARTITION clause to sequentially number groups of rows. Rows will be sequentially numbered within each unique partition value. The sequential number will restart at 1 for each new partition value in your record set. Take a look at the following query:

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person

When I run this query, this is my result set:

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
3                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
5                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

In this example I partitioned by Gender, and ordered by Age. Doing this allowed me to sequentially number the female records in my Person table by age, and then have the sequential numbering start over again for the male group.

RANK Function

Sometimes you want a row that has the same order by column value as another row to have the same ranking. If this is the case then the RANK() function will help you. The RANK function has the following calling syntax:

RANK ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Where the:
“<partition_by_clause>” is a column or set of columns used to determine the grouping in which the RANK function applies sequential numbering.
<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).
The RANK function sequentially numbers a record set, but when two rows have the same order by value then they get the same ranking. The ranking value still gets incremented when two rows have the same order by value, so that when a new ranking order by value is encountered the ranking value on that new row will be 1 more than the number of proceeding rows. Let me show you a couple of examples to help you better understand the RANK function.
In this first example I want to rank my record set by Age:

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age], 
       FirstName, 
       Age
  FROM Person

Here is the output for this command:

Rank by Age          FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
4                    Mary       11
4                    Sherry     11
6                    Sam        17
7                    Ted        23
7                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

By looking at this output you can see that whenever rows have the same Age value their “Rank by Age” value are the same. You can see this for “Doris” and “George”, “Mary” and “Sherry”, as well as “Ted” and “Marty”. Each of these row pairs have the same “Rank by Age” value. Note that “Doris” and “George” both have a ranking of 2, but the ranking for “Mary” the next unique Age doesn’t have a ranking value of 3, but instead has a ranking of 4. This is because “Mary” is the forth record returned in the record set, and the RANK() functions takes this into account when setting the ranking value of the next unique “Rank by Age” value.
If you want to have multiple rankings in your record set, where each ranking is for a specific group you need to use the “PARTITION BY” clause of the RANK function. Below is an example where I grouped my ranking by Gender, and ordered each ranking by Age:

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person

Here is the results of running the above query:

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
2                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
4                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

Here you can see that the “F” Gender started ranking at 1 and goes through 4, then the ranking starts over with 1 when the first “M” Gender is encountered.

DENSE_RANK Function

The DENSE_RANK function is similar to the RANK function, although this function doesn’t produce gaps in the ranking numbers. Instead this function sequentially ranks each unique ORDER BY value. With the DENSE_RANK function each row either has the same ranking as the preceeding row, or has a ranking 1 greater then the prior row. The DENSE_RANK function has the same syntax as the RANK function.
Here I use the DENSE_RANK function to rank all my Person records by Age:

SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age], 
       FirstName, 
       Age
  FROM Person

This code produces the following output:

Dense Rank by Age    FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
3                    Mary       11
3                    Sherry     11
4                    Sam        17
5                    Ted        23
5                    Marty      23
6                    Sue        29
7                    Frank      38
8                    John       40

Here you can see the “Dense Rank By Age” column numbers are sequential without any gaps. And when two rows have the same ORDER BY value they have the same ranking like “Ted” and “Marty”.

NTILE Function

The last ranking function is the NTILE function. This function is used to break up a record set into a specific number of groups. The NTILE function also uses similar syntax as the other ranking functions.
In this first example, I want to group my Person records into three different groups of records. I want these groups to be based on the Age column. To do that I would run the following T-SQL:

SELECT FirstName, 
       Age, 
       NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
  FROM Person

Here is my result set from the above T-SQL command:

FirstName  Age         Age Groups
---------- ----------- --------------------
Larry      5           1
Doris      6           1
George     6           1
Mary       11          1
Sherry     11          2
Sam        17          2
Ted        23          2
Marty      23          2
Sue        29          3
Frank      38          3
John       40          3

In my result set I ended up with three different “Age Groups”. The first age group goes from Age 5 to Age 11, the second age group goes from 11 to 23, and the last age group is 29 to 40. The NTILE function just evenly divides your record set into the number of groups the NTILE function requests. By using the NTILE function each record in a group is give the same ranking.
The NTILE function is very useful if you only want to return a specific grouping of records. Below is an example where I returned only the middle group (Age Group = 2) from my prior example:

SELECT FirstName, 
       Age, 
       Age AS [Age Group]
FROM ( SELECT FirstName, 
              Age, 
              NTILE(3) OVER (ORDER BY Age) AS AgeGroup
        FROM Person) A
WHERE AgeGroup = 2

Here you can see I only returned the second Age Group:

FirstName  Age         Age Group
---------- ----------- -----------
Sherry     11          11
Sam        17          17
Ted        23          23
Marty      23          23

Conclusion

Coding a process to sequential number your record sets used to take a number of lines of code. SQL Server 2005 solved this coding dilemma by providing some new ranking functions. Hopefully the next time you need to sequential number a record set, one of the new ranking functions in SQL Server 2005 will make your number task, a trivial task.