Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите разобрать deadlock  [new]
Gamih
Member

Откуда:
Сообщений: 2
Всем привет!
Помогите понять в чем бяда.
1 таблица, идут постоянные updatы по ключу. В чем проблема и как лечится такое?

CREATE TABLE [dbo].[PhotoViews] (
  [UserID] int NOT NULL,
  [PhotoID] int NOT NULL,
  [Age] tinyint NOT NULL,
  [Date] datetime DEFAULT getdate() NOT NULL,
  [AvailableTriesCount] tinyint DEFAULT 2 NOT NULL,
  [GameStatus] tinyint DEFAULT 0 NOT NULL,
  [CreateDate] datetime DEFAULT getdate() NOT NULL,
  [Variants] varchar(48) DEFAULT '' NOT NULL,
  [Selected] varchar(48) DEFAULT '' NOT NULL,
  CONSTRAINT [PhotoViews_uq] UNIQUE ([UserID])
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO


2013-06-20 11:19:59.26 spid23s     deadlock-list
2013-06-20 11:19:59.26 spid23s      deadlock victim=process86cfa088
2013-06-20 11:19:59.26 spid23s       process-list
2013-06-20 11:19:59.26 spid23s        process id=process8032f948 taskpriority=0 logused=0 waitresource=OBJECT: 6:437576597:0  waittime=1196 ownerId=7295983 transactionname=UPDATE lasttranstarted=2013-06-20T11:19:57.623 XDES=0x843b3740 lockMode=X schedulerid=1 kpid=4828 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-06-20T11:19:57.623 lastbatchcompleted=2013-06-20T11:19:57.623 clientapp=.Net SqlClient Data Provider hostname=WINFUSD31 hostpid=592 loginname=_prod isolationlevel=read committed (2) xactid=7295983 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2013-06-20 11:19:59.26 spid23s         executionStack
2013-06-20 11:19:59.26 spid23s          frame procname=adhoc line=1 stmtstart=166 sqlhandle=0x02000000dfa98e3808dc65b3bf3ec9fda11ab0651e8038fa
2013-06-20 11:19:59.26 spid23s     update photoviews set PhotoID=@p0, Age=@p1, Selected=@p2, AvailableTriesCount=@p3, GameStatus=@p4, Variants=@p5 where UserID=6985     
2013-06-20 11:19:59.26 spid23s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2013-06-20 11:19:59.26 spid23s     unknown     
2013-06-20 11:19:59.26 spid23s         inputbuf
2013-06-20 11:19:59.28 spid23s     (@p0 int,@p1 tinyint,@p2 nvarchar(4000),@p3 tinyint,@p4 tinyint,@p5 nvarchar(4000))update photoviews set PhotoID=@p0, Age=@p1, Selected=@p2, AvailableTriesCount=@p3, GameStatus=@p4, Variants=@p5 where UserID=6985    
2013-06-20 11:19:59.28 spid23s        process id=process86cfa088 taskpriority=0 logused=0 waitresource=OBJECT: 6:437576597:0  waittime=1196 ownerId=7295975 transactionname=UPDATE lasttranstarted=2013-06-20T11:19:56.837 XDES=0x843b2e90 lockMode=X schedulerid=1 kpid=4420 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-06-20T11:19:56.837 lastbatchcompleted=2013-06-20T11:19:56.837 clientapp=.Net SqlClient Data Provider hostname=WINFUSD31 hostpid=592 loginname=_prod isolationlevel=read committed (2) xactid=7295975 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2013-06-20 11:19:59.28 spid23s         executionStack
2013-06-20 11:19:59.28 spid23s          frame procname=adhoc line=1 stmtstart=112 sqlhandle=0x02000000cda70d04ce6b4790f6c2ff7989797cc1d38e153a
2013-06-20 11:19:59.28 spid23s     update photoviews set AvailableTriesCount=@p0, Selected=@p1, Age=@p2, GameStatus=@p3 where UserID=6825     
2013-06-20 11:19:59.28 spid23s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2013-06-20 11:19:59.28 spid23s     unknown     
2013-06-20 11:19:59.28 spid23s         inputbuf
2013-06-20 11:19:59.28 spid23s     (@p0 tinyint,@p1 nvarchar(4000),@p2 tinyint,@p3 tinyint)update photoviews set AvailableTriesCount=@p0, Selected=@p1, Age=@p2, GameStatus=@p3 where UserID=6825    
2013-06-20 11:19:59.28 spid23s        process id=process86cfae08 taskpriority=0 logused=0 waitresource=OBJECT: 6:437576597:0  waittime=1196 ownerId=7295961 transactionname=UPDATE lasttranstarted=2013-06-20T11:19:56.633 XDES=0x83f23970 lockMode=X schedulerid=1 kpid=2844 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-06-20T11:19:56.633 lastbatchcompleted=2013-06-20T11:19:56.633 clientapp=.Net SqlClient Data Provider hostname=WINFUSD31 hostpid=592 loginname=_prod isolationlevel=read committed (2) xactid=7295961 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2013-06-20 11:19:59.28 spid23s         executionStack
2013-06-20 11:19:59.28 spid23s          frame procname=adhoc line=1 stmtstart=112 sqlhandle=0x02000000aed452013364787e0e5ac61e29627bec287aa02e
2013-06-20 11:19:59.28 spid23s     update photoviews set AvailableTriesCount=@p0, Selected=@p1, Age=@p2, GameStatus=@p3 where UserID=7002     
2013-06-20 11:19:59.28 spid23s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2013-06-20 11:19:59.28 spid23s     unknown     
2013-06-20 11:19:59.28 spid23s         inputbuf
2013-06-20 11:19:59.28 spid23s     (@p0 tinyint,@p1 nvarchar(4000),@p2 tinyint,@p3 tinyint)update photoviews set AvailableTriesCount=@p0, Selected=@p1, Age=@p2, GameStatus=@p3 where UserID=7002    
2013-06-20 11:19:59.28 spid23s       resource-list
2013-06-20 11:19:59.28 spid23s        objectlock lockPartition=0 objid=437576597 subresource=FULL dbid=6 objectname=dbo.PhotoViews id=lock800bcf00 mode=IX associatedObjectId=437576597
2013-06-20 11:19:59.28 spid23s         owner-list
2013-06-20 11:19:59.28 spid23s          owner id=process86cfae08 mode=IX
2013-06-20 11:19:59.28 spid23s         waiter-list
2013-06-20 11:19:59.28 spid23s          waiter id=process8032f948 mode=X requestType=convert
2013-06-20 11:19:59.28 spid23s        objectlock lockPartition=0 objid=437576597 subresource=FULL dbid=6 objectname=dbo.PhotoViews id=lock800bcf00 mode=IX associatedObjectId=437576597
2013-06-20 11:19:59.28 spid23s         owner-list
2013-06-20 11:19:59.28 spid23s          owner id=process86cfae08 mode=IX
2013-06-20 11:19:59.28 spid23s         waiter-list
2013-06-20 11:19:59.28 spid23s          waiter id=process86cfa088 mode=X requestType=convert
2013-06-20 11:19:59.28 spid23s        objectlock lockPartition=0 objid=437576597 subresource=FULL dbid=6 objectname=dbo.PhotoViews id=lock800bcf00 mode=IX associatedObjectId=437576597
2013-06-20 11:19:59.28 spid23s         owner-list
2013-06-20 11:19:59.28 spid23s          owner id=process86cfa088 mode=IX
2013-06-20 11:19:59.28 spid23s         waiter-list
2013-06-20 11:19:59.28 spid23s          waiter id=process86cfae08 mode=X requestType=convert
20 июн 13, 11:31    [14458604]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать deadlock  [new]
Gamih
Member

Откуда:
Сообщений: 2
и еще в таблицу идут select по ключу только и инсерты (не часто)
20 июн 13, 11:33    [14458627]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать deadlock  [new]
Andy Dragon
Member

Откуда:
Сообщений: 7
Похоже у вас запрещены PAGE_LOCKS и/или ROW_LOCKS для кластерного индекса (если он есть, но вы его не показываете ?) или для всей таблицы-кучи.
Чтобы проверить эти настройки :
SELECT i.name,
          i.index_id,
       i.[allow_row_locks],
       i.[allow_page_locks]
FROM sys.indexes i
WHERE i.[object_id] = OBJECT_ID(N'[dbo].[PhotoViews]', N'U') 


Если есть запись с index_id = 0, значит это куча.
Есть с 1 - значит есть кластерный индекс.

Пересоздание CONSTRAINT [PhotoViews_uq] UNIQUE ([UserID]) как CLUSTERED должно решить проблему.
21 июн 13, 00:48    [14462982]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить