Geek asks and answers

How to allow SQL Server deadlocks - once the order changes and query reductions are exhausted?

I have two hypothetical queries:

UPDATE BankAccounts SET HomePhone = '+1 252-555-0912' 
WHERE AccountNumber = 14400000619

and

SELECT * FROM BankAccounts 
WHERE HomePhone = '555-1212'

on a hypothetical table without additional indexes:

CREATE TABLE BankAccounts 
( 
   AccountNumber bigint NOT NULL PRIMARY KEY CLUSTERED,
   FirstName nvarchar(50) NOT NULL,
   MiddleName nvarchar(50) NULL,
   LastName nvarchar(50) NOT NULL,
   HomePhone varchar(50) NULL,
   IsClosed tinyint DEFAULT 0
)

and everything will be great. If I add an index to HomePhone:

CREATE INDEX IX_BankAccounts_HomePhone ON BankAccounts 
( HomePhone)

Now my statement SELECTmay be a victim of deadlock :

A transaction (process identifier 169) has stalled on locking resources with another process and has been selected as a victim of a deadlock. Restart the transaction.

General recommendations are as follows:

  • access tables in the same order
  • keep transactions as short as possible

Except in this case:

  • I access the (single) table in the same order (1 select 1 - 1)
  • Transactions are a single statement. i can't get less than that

, ?

READ UNCOMMITTED (.. ), , .

, , KB Article 83252:

SQL Server -

... . .

1205, SQL Server .

, : " , "

- ?

+5
2

;

. , read-write ( - ). , (.. ). ( , ), - -.

, .

+3

SELECT * ( ), INCLUDE d , SELECT . SELECT .

, , , , SELECT. NOLOCK READPAST , ( READPAST, , ).

, SNAPSHOT ( , ).

( TABLOCKX SELECT. , , , concurrency)

+4

More articles:


All Articles