How do you work with MS SQL Server?



  • Started 3 requests to update 3 different tables and turned into Begin Tran and Commit Tran.

    The second request made a mistake, but the data in the first table were not down.

    Shouldn't it have been a roll?

    UPD

    I understand I should have used instead.

    BEGIN TRAN
    UPDATE 1
    UPDATE 2
    UPDARE 3
    COMMIT TRAN
    

    That's it.

        BEGIN TRAN
        UPDATE 1
        UPDATE 2
        UPDARE 3
    if @error<>0
        rollback tran
        COMMIT TRAN
    

    ?

    If that's true, you'll be on MSDN. https://msdn.microsoft.com/ru-ru/library/ms190295.aspx

    It's misleading, so it doesn't show the example of the error check. ♪ ♪

    I don't know what that is.

    I've done this experiment: First I do N once this request is:

    BEGIN TRAN
    INSERT INTO ttt VALUES  (GETDATE())
    

    -Translation isn't special

    Then I separately:

    COMMIT TRAN
    

    Then

    ROLLBACK tran
    

    I've got all my N Inserts rolled out, although I did COMMIT TRAN, and then ROLLBACK tran. The transaction was supposed to be over, and nothing was supposed to go off. ♪ ♪



  • In the case of construction

    BEGIN TRAN
    UPDATE 1
    UPDATE 2 --error
    UPDATE 3
    COMMIT TRAN
    

    (in the course of command not in the unit TRY ... CATCH ...(d) If on the second UPDATE There's gonna be a mistake, team performance can continue and reach. COMMIT

    Use of option

    BEGIN TRAN
    UPDATE 1
    UPDATE 2
    UPDATE 3
    IF @@ERROR <> 0
        ROLLBACK TRAN
    ELSE
        COMMIT TRAN
    

    It won't be the right thing, because the global variable @@ERROR contains the error number for the last completed team. This means: UPDATE 1 or UPDATE 2 ends with a mistake, but UPDATE 3 - no mistake, after. UPDATE 3 variable value @@ERROR It'll be fine. 0that would make a false conclusion about the success of the entire transaction.

    If you have to make a mistake, you can have two options.

    The first is the execution of teams on the block. TRY ... CATCH ...

    BEGIN TRY
        BEGIN TRAN
        UPDATE 1
        UPDATE 2 --error
        UPDATE 3
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
    END CATCH
    

    In this case, if a 2-step error arises, the execution will not continue, and it will be possible to move into a block. CATCHwhere forced ROLLBACK

    Second option - inclusion XACT_ABORT Before entering the transaction.

    SET XACT_ABORT ON
    BEGIN TRAN
    UPDATE 1
    UPDATE 2 --error
    UPDATE 3
    COMMIT TRAN
    

    In this case, when errors (defined, not all) occur on 2 mm UPDATE Team performance will be interrupted and changes will be automatically reversed. (Maybe this option should not be considered self-contained, in my view, inclusion XACT_ABORT - It's an additional means; I don't recall the case where I used this option separately to take off the transaction.

    In some cases both are used.

    Below is a little more detailed about the automatic and run-off.


    Automatic ROLLBACK

    ROLLBACK may occur automatically when the connection is closed if there are unfinished transactions for the connection. I mean, they created, for example, a table.

    create table test (id int primary key);
    

    Open the connection and carry it.

    begin tran;
    insert into test (id) values (1);
    select * from test;
    

    Close the connection without finishing the transaction. COMMITNo ROLLBACK did not. SqlServer will deactivate the transaction when the connection breaks. We'll see that it's empty.

    Also automatic ROLLBACK may occur when errors occur (such as a breach of PK, FK of limitations on entry or removal of data, for example) if the option is included https://msdn.microsoft.com/ru-ru/library/ms188792.aspx (on default) OFF) For example:

    set xact_abort on;
    begin tran;
    insert into test (id) values (2);
    select * from test;
    insert into test (id) values (2); --error: Violation of PK ...
    select * from test;
    commit tran;
    

    before the second select and before commit It won't work, and it'll happen automatically. Now with the switch off. xact_abort (what default):

    set xact_abort off;
    begin tran;
    insert into test (id) values (3);
    insert into test (id) values (3); -- error
    insert into test (id) values (4);
    commit tran;
    select * from test;
    

    Despite the mistake, it'll work. commit (No reset, respectively) select After him.

    Unfortunately, the op. set xact_abort on Not always. In particular, it does not deactivate the transaction in the generation of user exemptions (including generated in DML-triggers). For example:

    set xact_abort on;
    begin tran;
    insert into test (id) values (5);
    if not exists (select 1 from test where id = 0)
        raiserror('Bad data', 16, 1);
    commit tran;
    select * from test;
    

    Despite set xact_abort on and the generosity of the case will come to commit and before select After him. Therefore, a focused challenge can be more useful. rollback

    -- вернули опцию в состояние по-умолчанию, если она была оставлена в состоянии ON
    set xact_abort off; 
    

    Managed ROLLBACK

    frequently applied catch Block, when the trespassing is turned in try ... catch ... Construction:

    begin try
        begin tran;
        -- тут делаем что-то
        commit tran;
    end try
    begin catch
        rollback tran;
    end catch
    

    Privacy xact_abort off (i.e. default) ROLLBACK Not automatic if the transaction was open, but because of the error, it didn't. COMMIT♪ In this case, SqlServer allows the programmer to decide whether the roll will be useful in a mistake or not. Next, a few examples, where the offset can be useful catch and when it's bad.

    Example 1: Change in transaction data.

    May there be a procedure which in the transaction makes the data box in two related tables:

    create procedure dbo.SetUserInfo
    (
        @uid uniqueidentifier = NULL,
        @info xml
    )
    as
    begin try
        set nocount, xact_abort on;
    
    if @info is NULL or @info.exist('/User') = 0
    begin
        raiserror('No or bad data provided.', 16, 1);
        return;
    end;
    
    begin transaction;
    
    declare @inserted table (ID int not NULL);
    declare @id int;
    
    merge into dbo.Users t
    using(
        select
            @uid,
            @info.value('(/User/@FirstName)[1]', 'nvarchar(50)'),
            @info.value('(/User/@LastName)[1]', 'nvarchar(50)')
        ) s(UID, FirstName, LastName)
    on t.UID = s.UID
    when matched then
        update
        set t.FirstName = s.FirstName, t.LastName = s.LastName
    when not matched then
        insert (UID, FirstName, LastName)
        values (s.UID, s.FirstName, s.LastName)
    output inserted.ID into @inserted (ID)
        ;
    
    select @id = ID from @inserted;
    
    merge into dbo.UserContacts t
    using (
        select @id, ct.ID, x.c.value('@Value', 'nvarchar(400)')
        from @info.nodes('/User[1]/Contacts[1]/Contact') x(c)
            join dbo.UserContactTypes ct on ct.Type = x.c.value('@Type', 'nvarchar(400)')
    ) s (UserID, ContactTypeID, ContactInfo)
    on t.UserID = s.UserID and t.ContactTypeID = s.ContactTypeID
    when not matched by source then
        delete
    when matched then
        update
        set t.ContactInfo = s.ContactInfo
    when not matched then
        insert (UserID, ContactTypeID, ContactInfo)
        values (s.UserID, s.ContactTypeID, s.ContactInfo)
        ;
    
    commit transaction;
    

    end try
    begin catch
    declare
    @errMsg nvarchar(4000) = error_message(),
    @errLine int = error_line(),
    @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
    ;

    if @@trancount &gt; 0
        rollback transaction;
    
    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
    

    end catch
    GO

    Let's say that there's been a challenge to the procedure and the data entry has started. Let's say the box is in. Users it was successful, and when it was delivered UserContacts Conflict with a unique index (UserID, ContactTypeID) (because of, for example, @info one and the same <Contact Type="Phone" Value="0(000)000-00-00" /> I've been shy twice.

    If the logic of the application is dictated that either the substance is intact or not inserted at all, then catch done rollback (as in this example).

    But there may be a situation where errors resulting from certain individual requests do not constitute a serious reason for the reversal of all acts committed. For example, if we don't have two related tables but imported data into several independent tables, and we don't want to download the part of the data that has already been successfully introduced. Then, catch You can try. commit (not any error would make it possible, how to correctly do so in the next example).

    I mean. rollback I don't have to do anything wrong. Doing off or not depends on semantics of the data and logic of the application.

    Example 2: Reading data in transaction.

    Transactions to change data are quite common, but sometimes transaction needs reading. For such transactions, it is unthinkable rollback It's possible to withstand a favor.

    May there be a procedure in which repeatable read or snapshot Transactions read:

    create procedure dbo.GetSalesData
    (
    @dateFrom datetime,
    @dateTo datetime
    )
    as
    begin try
    set nocount on;

    declare @userID int;
    select @userID = UserID from #Session;
    
    if @userID is NULL
    begin
        raiserror('Access denied.', 16, 1);
        return;
    end;
    
    create table #Orders (OrderID int not NULL);
    alter table #Orders add primary key (OrderID);
    
    set transaction isolation level snapshot;
    begin transaction;
    
    insert into #Orders (OrderID)
    select op.OrderID
    from dbo.OrderPermissions(@userID) op
        join dbo.Orders ord on ord.ID = op.OrderID
    where op.[Permissions] &gt; 0
        and ord.[Date] &gt;= @dateFrom and ord.[Date] &lt; @dateTo
    
    -- some check based on #Order and other data
    if exists (select 1 from #Orders o join ... where ...)
    begin
        raiserror('Check fail.', 16, 1);
        return;
    end;
    
    select ...
    from dbo.Orders ord
        join #Orders o on o.OrderID = ord.ID
    
    select ...
    from dbo.Invoices inv
        join #Orders o on o.OrderID = inv.OrderID
    
    select ...
    from dbo.Shipment sh
        join #Orders o on o.OrderID = sh.OrderID
    
    commit transaction;
    

    end try
    begin catch
    declare
    @errMsg nvarchar(4000) = error_message(),
    @errLine int = error_line(),
    @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
    ;

    if xact_state() = 1
        commit transaction;
    else if xact_state() = -1
        rollback transaction;
    
    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
    

    end catch
    GO

    The procedure is as follows. The transaction is open. It fills the filtering table #Orders (to give the user only what he is allowed to see). Then some verification #Orders and other data. If the verification passes, the data shall be provided, if not, by error.

    Let's say this test wasn't successful. Transactions open and mistakes made raiserror('Check fail.', 16, 1)which is why the control is transferred to catch♪ Should I? catch There's going to happen. rollback? No. We're just reading data and we're not changing anything. #Orders) Furthermore, the table #Orders set up before entering the transaction and filled in the transaction. As a consequence, if we did rollback I'd start rolling the data in it, which is longer than commit and simple destruction #Orders I' leave the procedure. That is, in this case, catch Better try. committhe possibility or inability of which is determined by the function https://msdn.microsoft.com/ru-ru/library/ms189797.aspx ♪




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2