Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Когда ставить блокировки?  [new]
KamutaHirovata
Guest
Покритикуйте схему на предмет архитектурных про... проколов.
Есть программа учета платежек, упрощенно её можно изобразить следующими скриптами. (Процедурки могут выдавать Msg 266, но это не суть).

create table users (userId int not null primary key identity, balance money default(0), name nvarchar(64))

go
insert users (name) values (N'Вася'), (N'Саша'), (N'Зина'),(N'Сережа')
update users set balance = 100 where userId = 1;
go
create table payments (paymentId int not null primary key identity,
                       srcUserId  int not null references users(userId), 
                       dstUserId  int not null references users(userId), 
                       amount     money not null);
go                        
create table invoices (invoiceId  int not null primary key identity,
                       userId     int not null references users(userId), 
                       paymentId  int null references payments(paymentId),
                       isAccepted bit not null default(0));
go
-- процедура для перевода с одного кошелька на другой
-- проверяет баланс, инсертит транзакцию
create procedure createPayment 
    @srcUserId  int,
    @dstUserId  int,
    @amount     money,
    @paymentId  int = null output
as 
begin
    begin tran
    
    declare @balance money;
    select  @balance = balance
    from    users with (xlock) 
    where   userId =  @srcUserId
        
    if (@balance < @amount)
    begin
        if (@@trancount > 0) rollback;
        raiserror('not enough money', 18, 10);
        return;
    end
    
    insert payments(srcUserId,dstUserId,amount) values (@srcUserId,@dstUserId, @amount)
    
    set @paymentId = scope_identity()
    
    commit;
end
go
-- триггер, пересчитывает балансы при вставке.
-- так сложилось исторически, что делает это именно он.
create trigger payments_afterInsert on payments after insert
as 
begin
    update  u
    set     balance = 
                case 
                    when u.userId = i.srcUserId then u.balance - i.amount
                    when u.userId = i.dstUserId then u.balance + i.amount
                end
    from    users u
    join    inserted i on u.userId in (i.srcUserId,i.dstUserId)
end
go

-- создает сразу два перевода
-- откатывает оба, если один не создался
create procedure createCoulpePayments
    @srcUserId int,
    @dstUserId   int,
    @amount     money,
    @paymentId1 int = null output,
    @paymentId2 int = null output
as
begin
    begin tran
    begin try
        -- создаю первый
        exec createPayment @srcUserId, @dstUserId, @amount, @paymentId1 out;
        -- создаю второй
        exec createPayment @srcUserId, @dstUserId, @amount, @paymentId2 out;
        
        commit;
    end try
    begin catch
        if (@@trancount > 0) rollback tran;
        declare @errorMessage nvarchar(max)= error_message();
        raiserror(@errorMessage, 18, 10);
    end catch
    
end

go
Правильно ли накладываются блокировки, если учесть что используется RCSI? Какие видите узкие места? Правильно ли вешается X блокировка на баланс или можно обойтись другой?

Сейчас попробую еще вопросов сформулировать :)
29 июн 11, 14:00    [10892918]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
TranKiller
Guest
Нужно ли в выборке где блокируется баланс выбирать и тем самым блокировать обе записи?
29 июн 11, 14:26    [10893131]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
KamutaHirovata
Guest
Теперь, мне надо добавить номер документа в платеж, он должен быть уникальным в пределах отправителя пользователя, либо пустым.

alter table payments add docNumber int

Как в процедуре createPayment правильно заблокировать табличку payments, чтобы не допустить попадания дубликатов? Нужно блокировать всю таблицу?
29 июн 11, 14:48    [10893299]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
лолл
Member

Откуда:
Сообщений: 450
KamutaHirovata,

С вашей манерой реализации вложенных транзакций (вызов CreatePayment из CreateCouplePayments) вы рано или поздно нарветесь на ошибку 266 "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = %ld, current count = %ld."
29 июн 11, 14:59    [10893389]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
KamutaHirovata,

Во-первых, вместо
    declare @balance money;
    select  @balance = balance
    from    users with (xlock) 
    where   userId =  @srcUserId
        
    if (@balance < @amount)
    begin
        if (@@trancount > 0) rollback;
        raiserror('not enough money', 18, 10);
        return;
    end

    insert payments(srcUserId,dstUserId,amount) values (@srcUserId,@dstUserId, @amount)
Написать
    insert payments(srcUserId,dstUserId,amount)
    select
     @srcUserId,@dstUserId, @amount
    from
     users with (updlock)
    where
     userId = @srcUserId and
     balance < @amount

    if @@rowcount = 0
    begin
        if (@@trancount > 0) rollback;
        raiserror('not enough money', 18, 10);
        return;
    end
Во-вторых, ваш триггер не учитывает вставку более одной записи на сочетание srcUserId и dstUserId
В-третьих, про ошибку 266 вам уже сказали.
29 июн 11, 15:11    [10893482]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
KamutaHirovata
Guest
Почему xlock заменен на updlock?
29 июн 11, 15:25    [10893621]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
KamutaHirovata
Guest
лолл
KamutaHirovata,

С вашей манерой реализации вложенных транзакций (вызов CreatePayment из CreateCouplePayments) вы рано или поздно нарветесь на ошибку 266 "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = %ld, current count = %ld."


Я писал что Msg 266 возможен. Можно было конечно сделать нормально, но для примера не хотелось делать огород. На деле использую такой шаблон:
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
29 июн 11, 15:28    [10893650]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
KamutaHirovata,

А зачем сразу блокировать читателей?
29 июн 11, 15:37    [10893733]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
KamutaHirovata
Guest
invm
KamutaHirovata,

А зачем сразу блокировать читателей?


Почему же? Чтения доступны.

begin tran
select * from users with(xlock) where userId = 1
exec sp_lock @@spid
waitfor delay '0:30'
commit

Из другого коннекта читается еще до комита первой транзакции
select * from users where userId = 1
Уровень изоляции - read committed
29 июн 11, 16:26    [10894233]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
KamutaHirovata,

У вас RCSI что ли? Какую задачу пытаетесь решить?
29 июн 11, 16:48    [10894483]     Ответить | Цитировать Сообщить модератору
 Re: Когда ставить блокировки?  [new]
KamutaHirovata
Guest
invm
KamutaHirovata,

У вас RCSI что ли? Какую задачу пытаетесь решить?


Да я что-то запутался. Есть несколько дедлоков в разных местах, борюсь с ними. На продакшене RCSI включен. А вот на тестовой - нет, и чтения доступны. Что-то я совсем запутался.

DBCC USEROPTIONS
Set Option                Value
------------------------- --------------
textsize                  2147483647
language                  us_english
dateformat                mdy
datefirst                 7
lock_timeout              -1
quoted_identifier         SET
arithabort                SET
ansi_null_dflt_on         SET
ansi_warnings             SET
ansi_padding              SET
ansi_nulls                SET
concat_null_yields_null   SET
isolation level           read committed
30 июн 11, 08:36    [10896856]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить