Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

SQL Server: Simulate deadlock

For this deadlock simulation we need to have 2 tables, each one of them with at least one record:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AA]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[AA](

[A] [int] NULL

) ON [PRIMARY]

END

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BB]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BB](

[B] [int] NULL

) ON [PRIMARY]

END

GO


INSERT INTO AA (A) VALUES (1);

GO

INSERT INTO BB (B) VALUES (2);

GO


----------

Now we will open two sessions:

Session #1

Session #2

BEGIN TRANSACTION;


Update   AA

Set    A = 3;

 

WAITFOR DELAY '00:00:03';

 

Update   BB

Set    B = 4;

 

ROLLBACK TRANSACTION;

BEGIN TRANSACTION;

 

Update   BB

Set    B = 4;

 

WAITFOR DELAY '00:00:03';

 

Update   AA

Set    A = 3;

 

ROLLBACK TRANSACTION;

Start execute Session #1 and immediately execute Session #2.

The 2 sessions are waiting for each other: Session #1 lock AA, Session #2 lock BB.

This is a deadlock.

One of them will be chosen (By SQL Server) as a deadlock victim and will be "killed".



No comments:

Post a Comment