How do you work with MS SQL Server?

[esther]

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. ♪ ♪

[irl]

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 > 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] > 0
    and ord.[Date] >= @dateFrom and ord.[Date] < @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 ♪