Data Engineering with Avishkar: 04/01/2010

The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about. Maintaining integrity is of utmost importance for a database, so much so that we cannot trust users and applications to enforce these rules by themselves. Once integrity is lost, you may find customers are double billed, payments to the supplier are missing, and everyone loses faith in your application. We will be talking in the context of the SQL Server sample Northwind database.
Data integrity rules fall into one of three categories: entity, referential, and domain.

Check Constraints
Check constraints contain an expression the database will evaluate when you modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. You can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.

NOT NULL Constraints
A logically unknown expression happens when a NULL value is present in an expression.

Any SQL INSERT, UPDATE or DELETE command applied to a table with constraints enabled has the possibility of failing.

For example, updates applied to a Parent Table may fail if the statement leaves orphaned rows in a child table, INSERT command against a Child Table may fail if a matching foreign key value does not exist in the parent table.
Constraint failures will result in the statement being rolled back - coding an application front end to deal with such errors is generally easier than handling all the business rules in code.

Some applications require to ignore check constraints and NOT NULL constraints , arguing that the business logic of an application should be retained within the business logic layer of an application. However, in such a scenario, it becomes possible for invalid data to be added to the database. So we need to disable or drop these constraints.

Following is a one line command can do a thing like this:

-- Drop Check constraints
select 'ALTER TABLE ' + s.name+'.'+OBJECT_NAME(o.parent_object_id) + ' DROP CONSTRAINT ' +OBJECT_NAME(OBJECT_ID)
FROM sys.objects as o
join sys.schemas as s on s.schema_Id = o.schema_id
WHERE o.type_desc LIKE '%CHECK_CONSTRAINT'

-- Drop NOT NULL constraints on column and and allow NULL

select 'ALTER TABLE ' + s.name+'.'+o.name + ' ALTER COLUMN ' +c.name + ' '+ t.name +' NULL'
FROM sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type='U'
and c.isnullable=1

Deadlocks are a side effect of blocking and not a SQL Server Bug. Poorly written queries in SQL Server can trigger deadlock in the system. We all knows cyclic dependency causes dead lock.

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, P1 holds a lock on table A and requests a lock on table B; P2 holds a lock on table B and requests a lock on table A.

The lock manager(Lock Monitor)/ thread checks for deadlocks. When a lock manager/ deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID.

When SQL Server find a deadlock, it kill one process (deadlock victim) rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Database engine choose deadlock victim according to the least cost to rollback.

what can cause deadlocks?

• Locks

• Worker threads

• Memory

• Parallel query execution-related resources

• Multiple Active Result Sets (MARS) resources.

They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur.

Trace Flags can be used to output the details of deadlocks as well, and generally provide you the maximum amount of information to help facilitate identifying the root of the problem. In SQL 2000, flags 1204 (basic) and 1205 (detailed) provide information regarding deadlocks.

An Example statement to enable tracing to the ErrorLog for all connections:

DBCC TRACEON (1204, 3605, -1)

DBCC TRACEON (1205, 3605, -1)

DBCC TRACEON (1222, 3605, -1)

SQL Profiler

The SQL Profiler can also be used to help in identifying deadlocking. In order to identify the deadlocking statements in SQL Profiler, you need to be tracing the following events.

Locks - Lock:Deadlock

Locks - Lock:Deadlock Chain

Stored Procedure - SP:StmtStarting

TSQL - SQL:StmtStarting

How to create profiler trace to find deadlock?

1.Start SQL profiler

2.On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace

3.Click “Show all events”

4.Click the Events tab, above specified events

(i) Add the Deadlock graph event to the trace from the Locks category. You will get an additional tab appears on the Trace Properties window, called Event Extraction Settings.

(ii) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file.

When a deadlock is encountered, the Deadlock chain event will output the SPID's associated with the deadlock. From there, you can look at the StmtStarting event for the SPID's immediately following the Deadlock Event to isolate the problem statements.

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention.

To translate the database id and object id into names, you can do

SELECT database_name(DatabaseID)

SELECT object_name(ObjectID)

You can also run following query to check which process is being blocked.

SELECT * FROM sys.sysprocesses WHERE blocked <> 0

Get the SPID from blocked column

DBCC inputbuffer (SPID)

sp_who2 'active'

sp_lock2

How to reproduce a deadlock:

-- ==================================

-- Create Tables

-- ==================================

CREATE TABLE [dbo].[Customers](

[CustomerID] [int] NOT NULL,

[CustName] [nvarchar](50) NOT NULL,

[PhoneNumber] [nvarchar](24) NOT NULL,

[Email] [nvarchar](100) NULL

)

CREATE TABLE [dbo].[Products](

[ProductID] [int] NOT NULL,

[ProductName] [nvarchar](40) NOT NULL,

[Price] [decimal](18, 2) NOT NULL

)

CREATE TABLE [dbo].[Orders](

[OrderID] [int] NOT NULL,

[CustomerID] [int] NULL,

[ProductID] [int] NOT NULL,

[OrderDate] [datetime] NOT NULL,

[DeliveryDate] [datetime] NOT NULL

)

-- ==================================

-- Add rows

-- ==================================

insert into [Customers] values ( 1,'Aaron Bryant','754-555-0137','aaron.bryant@gmail.com')

insert into [Customers] values ( 2,'Abigail Bailey','329-555-0153','abigail.bailey@gmail.com')

insert into [Customers] values ( 3,'Adrian Gray','379-555-0152','adrian.gray@gmail.com')

insert into [Products] values ( 1,'Bacon ice cream',300)

insert into [Products] values ( 2,'Chocolate ',200)

insert into [Products] values ( 3,'Coffee',150)

insert into [Orders] values ( 1,2,3,'2007-07-04','2007-07-06')

insert into [Orders] values ( 2,2,2,'2007-07-14','2007-08-06')

insert into [Orders] values ( 3,3,3,'2007-09-04','2007-09-06')

insert into [Orders] values ( 4,1,3,'2007-11-14','2007-11-16')

-- ====================================

-- Execute this query using a query window for Deadlock Thread 1

-- ====================================

BEGIN TRANSACTION

UPDATE [Customers] SET [PhoneNumber] = '999-555-1212' WHERE [CustomerID] = 1

WAITFOR DELAY '00:00:10'

UPDATE [Products] SET [Price] = 120 WHERE [ProductID]= 2

ROLLBACK TRANSACTION

-- ====================================

-- Immediately execute this query using a query window for Deadlock Thread 2

-- ====================================

BEGIN TRANSACTION

UPDATE [Products] SET [ProductName] = 'Ice cream' WHERE [ProductID]=1

UPDATE [Customers] SET [CustName] = 'Mr Abigail Bailey' WHERE [CustomerID] = 2

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION

All transactions in a deadlock will wait indefinitely unless one of the participating transactions is rolled back, for example, because its session was terminated.

The application with the transaction chosen as the deadlock victim should retry the transaction, which usually completes after the other transaction or transactions involved in the deadlock have finished.

View and save a deadlock graph in XML

Viewing a deadlock graph in XML format allows you to copy the inputbuffer of Transact-SQL statements involved in the deadlock. You may also prefer to analyze deadlocks in a text-based format.

If you have used a Transact-SQL query to return deadlock graph information, to view the deadlock graph XML, select the value in the deadlock_xml column from any row to open the deadlock graph's XML in a new window in SSMS.

<victim-list>

</victim-list>

<process-list>

unknown </frame>

unknown </frame>

</executionStack>

-- ====================================

-- Immediately execute this query using a query window for Deadlock Thread 2

-- ====================================

BEGIN TRANSACTION

UPDATE [Products] SET [ProductName] = 'Ice cream' WHERE [ProductID]=1

UPDATE [Customers] SET [CustName] = 'Mr Abigail Bailey' WHERE [CustomerID] = 2

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION </inputbuf>

</process>

unknown </frame>

unknown </frame>

</executionStack>

-- ====================================

-- Execute this query using a query window for Deadlock Thread 1

-- ====================================

BEGIN TRANSACTION

UPDATE [Customers] SET [PhoneNumber] = '999-555-1212' WHERE [CustomerID] = 1

WAITFOR DELAY '00:00:10'

UPDATE [Products] SET [Price] = 120 WHERE [ProductID]= 2

ROLLBACK TRANSACTION </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

The XML for this example deadlock graph is:

A deadlock graph typically has three nodes:

Victim-list. The deadlock victim process identifier.

Process-list. Information on all the processes involved in the deadlock. Deadlock graphs use the term 'process' to represent a session running a transaction.

Resource-list. Information about the resources involved in the deadlock.

Data Engineering with Avishkar

April 16, 2010

Dropping/Disabling Check Constraints and NOT NULL constraints in SQl Server

April 12, 2010

Deadlocks in SQL Server

Secure a Microsoft Fabric data warehouse

Search This Blog