Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Имеем следующее

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

10.50.1777.0 RTM Enterprise Edition (64-bit)


Схема

use poisoned;
go
if object_id('dbo.transfer','U') is not null
    drop table dbo.transfer
if object_id('dbo.account','U') is not null
    drop table dbo.account
go
-- счета
create table account (accountId int not null primary key identity(1,1), balance money not null default(0));
go
-- переводы
create table transfer (transferId int not null primary key identity(1,1),
                       srcAccountId int not null,
                       dstAccountId int not null,
                       externalId nvarchar(64) null,
                       amount money not null,
                       createDate datetime default(getutcdate()),
                       foreign key (srcAccountId) references account(accountId),
                       foreign key (dstAccountId) references account(accountId));
go
-- уникальный идекс на "внешний" идентификатор перевода, т.е. в пределах отправителя может быть сколько угодно
-- переводов с NULL в externalId либо один и только один с конкретным значением
create unique nonclustered index ix_transfer_srcAccountId_externalId_unique on dbo.transfer
(
	srcAccountId asc,
	externalId   asc
)
where (externalId is not null)

go

-- создаем два счета для тестов
insert account values (100)
insert account values (0) 


Постоянно выполняются примерно такие процедуры, очень "конкурентный" участок

begin transaction

declare @srcAccountId int = 1,
        @dstAccountId int = 2,
        @transferAmount money = 1.00,
        @transferExternaiId nvarchar(64) = 'trn#0002';

declare @existsingTransferId int;

select  @existsingtransferid = t.transferId
from    dbo.transfer t with(xlock)
where   t.srcAccountId = @srcAccountId
and     t.externalid   = @transferExternaiId
and     @transferExternaiId is not null

if (@existsingtransferid is not null)
begin
    RaisError('order_id_already_used', 18, 1);
    return;
end

insert  dbo.transfer (srcAccountId, dstAccountId, externalId, amount)
values (@srcAccountId, @dstAccountId, @transferExternaiId, @transferAmount)

select * from transfer where transferId = scope_identity()

commit


Так вот, проблема в том что иногда в логе встречаются записи с ошибками вставки дубликата ключа в индекс, вместо удобоваримого исключения. Как такое получается не пойму. Помогите
26 янв 12, 11:50    [11974237]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
LeadyGaaga
Member

Откуда:
Сообщений: 37
А вы попробуйте посмотреть блокировки после select @existsingTransferId

SELECT *
FROM sys.dm_tran_locks dtl
WHERE dtl.request_session_id = @@SPID

Если бы ключ существовал, то экслюзивная блокировка бы сохранилась до конда транзакции. Вам нужно 2 хинта (XLOCK, HOLDLOCK).
26 янв 12, 12:29    [11974580]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Так а на что должен накладываться ваш xlock, если записи нет?
Вам нужен serializable-уровень транзакции, чтобы заблокировать диапазон ключей.
26 янв 12, 12:41    [11974684]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
А есть варианты при RCSI?
26 янв 12, 13:12    [11975004]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Объясню, это самое узкое место системы и поэтому хочется сохранить максимальную конкурентность. Вот думаю варианты. Может быть можно создать невыборочный индекс по отправителю и блокировать его на время вставки? Чтобы в тоже самое время транзакции других отправителей могли вставляться без блокировки.
26 янв 12, 13:29    [11975218]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
А просто try/catch написать и обработать ошибку?
26 янв 12, 13:33    [11975274]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Смущает немного идейные соображения, но наверно так и сделаю.
26 янв 12, 13:51    [11975472]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Сделал try/catch с проверкой

declare @error_number int = error_number(),
        @errro_message nvarchar(max) = error_message();
if (@error_number = 2601 and @errro_message like '%violated_index_name%')
begin 
  -- todo
end
else begin
 -- что-то вроде rethrow
end


А вообще какие еще варианты у меня были? Я что-то кроме tablockx не придумал ничего, но в моей ситуации это зло.
26 янв 12, 16:40    [11977319]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
exclusive lock
Смущает немного идейные соображения, но наверно так и сделаю.


я не понял, а чем вариант с xlock, holdlock не устраивает?
готовим тест

-- drop table a
-- create table a ( id int primary key )
insert into a select 10
insert into a select 20
insert into a select 30

делаем раз

begin tran
select * from a with (xlock) where id = 15
exec sp_lock @@spid
rollback

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
54 5 0 0 DB S GRANT
54 1 1131151075 0 TAB IS GRANT
54 5 1269771822 1 PAG 1:288223 IX GRANT
54 5 1269771822 0 TAB IX GRANT


делаем два

begin tran
select * from a with (xlock, holdlock) where id = 15
exec sp_lock @@spid
rollback

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
54 5 0 0 DB S GRANT
54 1 1131151075 0 TAB IS GRANT
54 5 1269771822 1 PAG 1:288223 IX GRANT
54 5 1269771822 1 KEY (286fc18d83ea) RangeX-X GRANT
54 5 1269771822 0 TAB IX GRANT

а то если у вас этот код вызывается из другого кода из-за try/catch могут быть очень веселые интересности
26 янв 12, 17:43    [11978110]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
exclusive lock
Сделал try/catch с проверкой

declare @error_number int = error_number(),
        @errro_message nvarchar(max) = error_message();
if (@error_number = 2601 and @errro_message like '%violated_index_name%')
begin 
  -- todo
end
else begin
 -- что-то вроде rethrow
end


В этом коде при rethrow возможны проблемы, если транзакция окажется в uncommittable state. Мы их обошли путем открытия транзакции до всего блока, затем создания savepoint. При uncommittable state делаем rollback "имя точки сохранения". Это приводит к ошибке, и при такой ошибке батч прерывается, что и нужно.
Решали практически идентичную проблему.

exclusive lock
А вообще какие еще варианты у меня были? Я что-то кроме tablockx не придумал ничего, но в моей ситуации это зло.

Есть вариант с (xlock, holdlock) как пишет Crimean.
Для высоконагруженных систем такой подход не работает потому что запрос блокирует чуть больше записей чем нужно, и система перестает успевать обрабатывать запросы
26 янв 12, 18:21    [11978542]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Crimean,

а какие проблемы с вложенным try/catch?

andsm, для всех процедур (почти всех) у меня шаблон. Я его где-то уже публиковал, он вроде все это рулит:

ALTER PROCEDURE [dbo].[__Template]
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ProcID CHAR(36) = NEWID();

    DECLARE @TranCount Int = @@TranCount;

    IF (@TranCount = 0)
        BEGIN TRANSACTION;
    ELSE
        SAVE TRANSACTION @ProcID;

    BEGIN TRY
        -- TODO: Procedure body comes here
        PRINT 'Procedure body';
        -- Завершаем транзакцию
        IF (@@TranCount > @TranCount)
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF (@@TranCount > 0)
        BEGIN
            IF (@@TranCount = @TranCount AND XACT_STATE() <> -1)
                ROLLBACK TRANSACTION @ProcID;
            ELSE IF (@@TranCount > @TranCount) 
                ROLLBACK TRANSACTION;
        END
        EXEC ReThrowException; 
        RETURN;
    END CATCH
END
26 янв 12, 18:33    [11978625]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Crimean,

Неправильно прочитал твоё сообщение, но все равно, какие интересности?
26 янв 12, 18:35    [11978637]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
LeadyGaaga
Member

Откуда:
Сообщений: 37
У меня вот такой вариант оставляет X блокировку на ключе до конца транзакции, даже без HOLDLOCK.
BEGIN TRAN 

insert  dbo.transfer (srcAccountId, dstAccountId, externalId, amount)
select @srcAccountId, @dstAccountId, @transferExternaiId, @transferAmount
WHERE NOT EXISTS 
                 (
                   SELECT *
                   FROM dbo.transfer AS x WITH(XLOCK)
                   WHERE
                         t.srcAccountId = @srcAccountId
                     and t.externalid   = @transferExternaiId
                     and t.transferExternaiId is not null	
                 	
                 )
  IF @@ROWCOUNT = 0 
	RAISERROR('order_id_already_used', 18, 1);
  
   SELECT *
   FROM sys.dm_tran_locks dtl
   WHERE dtl.request_session_id = @@SPID   
   

COMMIT TRAN 
	                	
26 янв 12, 18:52    [11978734]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
LeadyGaaga
У меня вот такой вариант оставляет X блокировку на ключе до конца транзакции, даже без HOLDLOCK.


так у вас INSERT
26 янв 12, 19:02    [11978786]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
exclusive lock
Crimean,

Неправильно прочитал твоё сообщение, но все равно, какие интересности?


1.пока нет throw
2.в catch иногда обязательно нужно делать rollback, это не всегда приемлемо
3.далеко не все ошибки ловятся в catch - в connect есть перечень что не ловится
26 янв 12, 19:17    [11978843]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
LeadyGaaga
Member

Откуда:
Сообщений: 37
Knyazev Alexey
так у вас INSERT


Так в оригинальной процедуре получуние идентификатора, если NULL (уже такой есть), то эксепшен иначе вставка такой строки.
26 янв 12, 19:21    [11978856]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
Crimean,

Ну у меня кейс когда я ожидаю и проверяю конкретную ошибку. К остальным - вызывающий вроде как готов. Там кстати и так в оригинальности варианте уже был внешний try в начале процедуры. Сейчас просто добавил еще один вокруг самой вставки.
26 янв 12, 19:23    [11978867]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
exclusive lock
Guest
LeadyGaaga,

Ну в оригинальной задаче между.вычиткгй и вставкой есть другие действия, хотя если немного потанцевать то в принципе можно твой вариант адаптировать.
26 янв 12, 19:26    [11978884]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
exclusive lock
andsm, для всех процедур (почти всех) у меня шаблон. Я его где-то уже публиковал, он вроде все это рулит:

....

Этот шаблон нормально работает при условии что везде для обработки ошибок используется try/catch. Если хотя бы в части хранимок обработка ошибок на основе @@error - возможны проблемы.
26 янв 12, 19:44    [11978977]     Ответить | Цитировать Сообщить модератору
 Re: XLOCK не спасает. Как так?  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
LeadyGaaga
У меня вот такой вариант оставляет X блокировку на ключе до конца транзакции, даже без HOLDLOCK.
...

Этот код не является решением описанной проблемы, и никакой его адаптацией проблема не решается. Код выполняется не мгновенно а за конечное время, и есть момент времени когда проверка на not exists закончена а Insert еще не начался. Вот в это время и может пройти аналогичная транзакция.
26 янв 12, 19:48    [11978994]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить