SQL Server/T-SQL Tutorial/Transaction/TRANSACTION

Материал из SQL эксперт
Версия от 13:24, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

A script with nested transactions

   <source lang="sql">

5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO

1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO

1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO

1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO

1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO

1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO

1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO

1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO

1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO

1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO

1> 2> 3> 4> create table Bankers( 5> BankerID Integer, 6> BankerName VARCHAR(20), 7> BankerContactLName VARCHAR(20), 8> BankerContactFName VARCHAR(20), 9> BankerCity VARCHAR(20), 10> BankerState VARCHAR(20), 11> BankerZipCode VARCHAR(20), 12> BankerPhone VARCHAR(20) 13> ) 14> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO

1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO

1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO

1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO

1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO

1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO

1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO

1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO

1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO

1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO

1> 2> 3> 4> 5> BEGIN TRAN 6> PRINT "First Tran @@TRANCOUNT: " + CONVERT(varchar,@@TRANCOUNT) 7> DELETE Billings 8> BEGIN TRAN 9> PRINT "Second Tran @@TRANCOUNT: " + CONVERT(varchar,@@TRANCOUNT) 10> DELETE Bankers 11> COMMIT TRAN 12> 13> PRINT "COMMIT @@TRANCOUNT: " + CONVERT(varchar,@@TRANCOUNT) 14> ROLLBACK TRAN 15> PRINT "ROLLBACK @@TRANCOUNT: " + CONVERT(varchar,@@TRANCOUNT) 16> SELECT "Bankers count: " + CONVERT(varchar,COUNT(*)) FROM Bankers 17> SELECT "Billings count: " + CONVERT(varchar,COUNT(*)) FROM Billings 18> GO First Tran @@TRANCOUNT: 2 (10 rows affected) (10 rows affected) Second Tran @@TRANCOUNT: 3 (10 rows affected) (10 rows affected) COMMIT @@TRANCOUNT: 2 (10 rows affected) ROLLBACK @@TRANCOUNT: 0 Msg 208, Level 16, State 1, Server J\SQLEXPRESS, Line 16 Invalid object name "Bankers". 1> 2> drop table Bankers; 3> GO Msg 3701, Level 11, State 5, Server J\SQLEXPRESS, Line 2 Cannot drop the table "Bankers", because it does not exist or you do not have permission. 1> 2> drop table Billings; 3> GO 1></source>


A transaction is bound by the ACID test. ACID stands for Atomicity, Consistency, Isolation (or Independence), and Durability:

   <source lang="sql">

Atomicity means that the transactions are an all-or-nothing entity carrying out all steps or none at all Consistency ensures that the data is valid both before and after the transaction. Data integrity must be maintained (foreign key references, for example) and internal data structures need to be in a valid state. Isolation is a requirement that transactions not be dependent on other transactions that may be taking place concurrently (either at the same time or overlapping). One transaction can�t see another transaction�s data that is in an intermediate state, but instead sees the data as it was either before the transaction began or after. Durability means that the transaction"s effects are permanent after the transaction has committed, and any changes will survive system failures. Quote from: SQL Server 2005 T-SQL Recipes A Problem-Solution Approach</source>


BEGIN TRANSACTION

   <source lang="sql">

4> IF EXISTS (SELECT * FROM sysobjects WHERE name="show_error" AND 5> type="U") 6> DROP TABLE show_error 7> GO 1> 2> CREATE TABLE show_error 3> ( 4> col1 smallint NOT NULL PRIMARY KEY, 5> col2 smallint NOT NULL 6> ) 7> GO 1> 2> 3> BEGIN TRANSACTION 4> 5> INSERT show_error VALUES (1, 1) 6> INSERT show_error VALUES (1, 2) 7> INSERT show_error VALUES (2, 2) 8> 9> COMMIT TRANSACTION 10> GO (1 rows affected) Msg 2627, Level 14, State 1, Server J\SQLEXPRESS, Line 6 Violation of PRIMARY KEY constraint "PK__show_error__74450BBF". Cannot insert duplicate key in object "dbo.show_error". The statement has been terminated. (1 rows affected) 1> 2> SELECT * FROM show_error 3> GO col1 col2

------ 
    1      1
    2      2

(2 rows affected) 1> 2> drop table show_error; 3> GO</source>


Declare variable in a transaction

   <source lang="sql">

2> CREATE TABLE employee( 3> id INTEGER NOT NULL PRIMARY KEY, 4> first_name VARCHAR(10), 5> last_name VARCHAR(10), 6> salary DECIMAL(10,2), 7> start_Date DATETIME, 8> region VARCHAR(10), 9> city VARCHAR(20), 10> managerid INTEGER 11> ); 12> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> 4> SET QUOTED_IDENTIFIER OFF 5> GO 1> 2> BEGIN TRAN Restore_Value 3> DECLARE @ValueToUpdate VARCHAR(30) 4> SET @ValueToUpdate = "Blankets" 5> UPDATE Employee 6> SET First_name = @ValueToUpdate 7> ROLLBACK TRAN 8> GO (9 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> drop table employee; 4> GO</source>


Exception before transaction committing

   <source lang="sql">

6> CREATE TABLE authors( 7> au_id varchar(11), 8> au_lname varchar(40) NOT NULL, 9> au_fname varchar(20) NOT NULL, 10> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 11> address varchar(40) NULL, 12> city varchar(20) NULL, 13> state char(2) NULL, 14> zip char(5) NULL, 15> contract bit NOT NULL 16> ) 17> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO 1> 2> CREATE TABLE jobs( 3> job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, 4> job_desc varchar(50) NOT NULL DEFAULT "New Position - title not formalized yet", 5> min_lvl tinyint NOT NULL CHECK (min_lvl >= 10), 6> max_lvl tinyint NOT NULL CHECK (max_lvl <= 250) 7> ) 8> GO 1> 2> 3> insert jobs values ("Coder", 10, 10) 4> insert jobs values ("Tester", 200, 250) 5> insert jobs values ("Programmer", 175, 225) 6> insert jobs values ("Painter", 175, 250) 7> insert jobs values ("Drawer", 150, 250) 8> insert jobs values ("Editor", 140, 225) 9> insert jobs values ("Manager", 120, 200) 10> insert jobs values ("Manager", 100, 175) 11> insert jobs values ("Representative", 25, 100) 12> insert jobs values ("Designer", 25, 100) 13> 14> GO 1> 2> 3> BEGIN TRAN 4> UPDATE authors 5> SET state = "FL" 6> WHERE state = "KS" 7> 8> IF @@ERROR <> 0 BEGIN 9> ROLLBACK TRAN 10> GOTO ON_ERROR 11> END 12> UPDATE jobs 13> SET min_lvl = min_lvl - 10 14> IF @@ERROR <> 0 BEGIN 15> ROLLBACK TRAN 16> GOTO ON_ERROR 17> END 18> 19> COMMIT TRAN 20> ON_ERROR: 21> 22> SELECT * FROM authors 23> WHERE state = "FL" 24> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 12 The UPDATE statement conflicted with the CHECK constraint "CK__jobs__min_lvl__5B0F49A1". The conflict occurred in database "master", table "dbo.jobs", column "min_lvl". The statement has been terminated. au_id au_lname au_fname phone address city state zip contract

---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------

1> 2> drop table authors; 3> drop table jobs; 4> GO</source>


Explicit Transaction Commands

   <source lang="sql">

Command Description BEGIN TRANSACTION Sets the starting point of an explicit transaction. ROLLBACK TRANSACTION Restores original data modified by a transaction, and brings data back to the state it was in at the start of the transaction. Resources held by the transaction are freed. COMMIT TRANSACTION Ends the transaction if no errors were encountered and makes changes permanent. Resources held by the transaction are freed. BEGIN DISTRIBUTED TRANSACTION Allows you to define the beginning of a distributed transaction to be managed by Microsoft Distributed Transaction Coordinator (MS DTC). MS DTC must be running locally and remotely. SAVE TRANSACTION SAVE TRANSACTION issues a savepoint within a transaction, which allows one to define a location to which a transaction can return if part of the transaction is cancelled. A transaction must be rolled back or committed immediately after rolling back to a savepoint. @@TRANCOUNT Returns the number of active transactions for the connection. BEGIN TRANSACTION increments @@TRANCOUNT by 1, and ROLLBACK TRANSACTION and COMMIT TRANSACTION decrements @@TRANCOUNT by 1. ROLLBACK TRANSACTION to a savepoint has no impact. Quote from: SQL Server 2005 T-SQL Recipes A Problem-Solution Approach</source>


Forcing an exclusive table lock.

   <source lang="sql">

CREATE TABLE authors( 

  au_id          varchar(11),
  au_lname       varchar(40)       NOT NULL,
  au_fname       varchar(20)       NOT NULL,
  phone          char(12)          NOT NULL DEFAULT ("UNKNOWN"),
  address        varchar(40)           NULL,
  city           varchar(20)           NULL,
  state          char(2)               NULL,
  zip            char(5)               NULL,
  contract       bit               NOT NULL

) GO insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) GO


BEGIN TRAN SELECT * FROM authors (tablockx) WHERE au_lname = "Green" WAITFOR DELAY "00:02:00" ROLLBACK TRAN GO drop table authors; GO</source>


Inserting a Row into MyTable and Rolling Back the Transaction

   <source lang="sql">

4> 5> 6> CREATE TABLE MyTable ( 7> key_col int NOT NULL IDENTITY (1,1), 8> abc char(1) NOT NULL 9> ) 10> INSERT INTO MyTable VALUES ("a") 11> INSERT INTO MyTable VALUES ("b") 12> INSERT INTO MyTable VALUES ("c") 13> SELECT * FROM MyTable ORDER BY key_col 14> 15> BEGIN TRAN 16> INSERT INTO MyTable VALUES ("e") 17> ROLLBACK TRAN 18> INSERT INTO MyTable VALUES ("f") 19> SELECT 20> * 21> FROM 22> MyTable 23> ORDER BY 24> key_col 25> 26> 27> drop table MyTable 28> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc

--- 
         1 a
         2 b
         3 c

(1 rows affected) (1 rows affected) key_col abc

--- 
         1 a
         2 b
         3 c
         5 f

(4 rows affected) 1></source>


One batch that contains two transactions

   <source lang="sql">

5> 6> 7> 8> CREATE TABLE authors( 9> au_id varchar(11), 10> au_lname varchar(40) NOT NULL, 11> au_fname varchar(20) NOT NULL, 12> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 13> address varchar(40) NULL, 14> city varchar(20) NULL, 15> state char(2) NULL, 16> zip char(5) NULL, 17> contract bit NOT NULL 18> ) 19> GO 1> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 2> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 3> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 4> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE publishers( 3> pub_id char(4) NOT NULL, 4> pub_name varchar(40) NULL, 5> city varchar(20) NULL, 6> state char(2) NULL, 7> country varchar(30) NULL DEFAULT("USA") 8> ) 9> GO 1> 2> 3> insert publishers values("1", "Publisher A", "Vancouver", "MA", "USA") 4> insert publishers values("2", "Publisher B", "Washington", "DC", "USA") 5> insert publishers values("3", "Publisher C", "Berkeley", "CA", "USA") 6> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> BEGIN TRAN 4> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 5> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 6> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 7> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 8> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 9> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 10> 11> SELECT * FROM authors 12> COMMIT TRAN 13> BEGIN TRAN 14> insert publishers values("4", "Publisher D", "New York", "NY", "USA") 15> insert publishers values("5", "Publisher E", "Chicago", "IL", "USA") 16> insert publishers values("6", "Publisher F", "Dallas", "TX", "USA") 17> insert publishers values("7", "Publisher G", "Vancouver", "BC", "Canada") 18> insert publishers values("8", "Publisher H", "Paris", NULL, "France") 19> COMMIT TRAN 20> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) au_id au_lname au_fname phone address city state zip contract

---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------

7 White Sylvia 777 777-7777 1 Pl. Rockville MD 77777 1 8 Yellow Heather 888 888-8888 3 Pu Vacaville CA 88888 0 9 Gold Dep 999 999-9999 5 Av. Oakland CA 99999 0 10 Siler Dean 000 000-0000 4 Av. Oakland CA 00000 1 1 Joe Abra 111 111-1111 6 St. Berkeley CA 11111 1 2 Jack Majo 222 222-2222 3 St. Oakland CA 22222 1 3 Pink Cherry 333 333-3333 5 Ln. Vancouver BC 33333 1 4 Blue Albert 444 444-4444 7 Av. Vancouver BC 44444 1 5 Red Anne 555 555-5555 6 Av. Regina SK 55555 1 6 Black Michel 666 666-6666 3 Pl. Regina SK 66666 1 (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> drop table authors; 3> drop table publishers; 4> GO 1> 2></source>


statements coded as a transaction

   <source lang="sql">

10> create table Billings ( 11> BankerID INTEGER, 12> BillingNumber INTEGER, 13> BillingDate datetime, 14> BillingTotal INTEGER, 15> TermsID INTEGER, 16> BillingDueDate datetime , 17> PaymentTotal INTEGER, 18> CreditTotal INTEGER 19> 20> ); 21> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> 4> DECLARE @BillingID int 5> BEGIN TRAN 6> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 7> IF @@ERROR = 0 8> BEGIN 9> SET @BillingID = @@IDENTITY 10> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 11> IF @@ERROR = 0 12> BEGIN 13> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 14> IF @@ERROR = 0 15> COMMIT TRAN 16> ELSE 17> ROLLBACK TRAN 18> END 19> ELSE 20> ROLLBACK TRAN 21> END 22> ELSE 23> ROLLBACK TRAN 24> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> drop table Billings; 4> GO 1></source>


Transaction spread across batches:

   <source lang="sql">

7> CREATE TABLE authors( 8> au_id varchar(11), 9> au_lname varchar(40) NOT NULL, 10> au_fname varchar(20) NOT NULL, 11> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 12> address varchar(40) NULL, 13> city varchar(20) NULL, 14> state char(2) NULL, 15> zip char(5) NULL, 16> contract bit NOT NULL 17> ) 18> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> GO 1> 2> CREATE TABLE publishers( 3> pub_id char(4) NOT NULL, 4> pub_name varchar(40) NULL, 5> city varchar(20) NULL, 6> state char(2) NULL, 7> country varchar(30) NULL DEFAULT("USA") 8> ) 9> GO 1> 2> 3> insert publishers values("1", "Publisher A", "Vancouver", "MA", "USA") 4> insert publishers values("2", "Publisher B", "Washington", "DC", "USA") 5> insert publishers values("3", "Publisher C", "Berkeley", "CA", "USA") 6> insert publishers values("4", "Publisher D", "New York", "NY", "USA") 7> GO 1> 2> 3> BEGIN TRAN 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO 1> SELECT * FROM authors 2> GO au_id au_lname au_fname phone address city state zip contract

---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------

1 Joe Abra 111 111-1111 6 St. Berkeley CA 11111 1 2 Jack Majo 222 222-2222 3 St. Oakland CA 22222 1 3 Pink Cherry 333 333-3333 5 Ln. Vancouver BC 33333 1 4 Blue Albert 444 444-4444 7 Av. Vancouver BC 44444 1 5 Red Anne 555 555-5555 6 Av. Regina SK 55555 1 6 Black Michel 666 666-6666 3 Pl. Regina SK 66666 1 7 White Sylvia 777 777-7777 1 Pl. Rockville MD 77777 1 8 Yellow Heather 888 888-8888 3 Pu Vacaville CA 88888 0 9 Gold Dep 999 999-9999 5 Av. Oakland CA 99999 0 10 Siler Dean 000 000-0000 4 Av. Oakland CA 00000 1 1> insert publishers values("5", "Publisher E", "Chicago", "IL", "USA") 2> insert publishers values("6", "Publisher F", "Dallas", "TX", "USA") 3> insert publishers values("7", "Publisher G", "Vancouver", "BC", "Canada") 4> insert publishers values("8", "Publisher H", "Paris", NULL, "France") 5> GO 1> COMMIT TRAN 2> GO 1> 2> 3> drop table authors; 4> drop table publishers; 5> GO</source>


Using Explicit Transactions

   <source lang="sql">

3> 4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> SELECT COUNT(*) BeforeCount FROM employee 4> -- Variable to hold the latest error integer value 5> 6> DECLARE @Error int 7> 8> BEGIN TRANSACTION 9> 10> INSERT employee(ID, first_Name)VALUES (10, "A") 11> SET @Error = @@ERROR 12> IF (@Error<> 0) GOTO Error_Handler 13> INSERT employee(ID, first_Name)VALUES (11, "E") 14> SET @Error = @@ERROR 15> IF (@Error <> 0) GOTO Error_Handler 16> COMMIT TRAN 17> Error_Handler: 18> IF @Error <> 0 19> BEGIN 20> ROLLBACK TRANSACTION 21> END 22> GO BeforeCount 

         9

(1 rows affected) (1 rows affected) 1> 2> SELECT COUNT(*) AfterCount FROM employee 3> GO AfterCount 

        11

(1 rows affected) 1> 2> 3> 4> drop table employee; 5> GO 1></source>


Using Transactions

   <source lang="sql">

To begin a transaction, issue the BEGIN TRANSACTION (or BEGIN TRAN) statement. This tells SQL Server that all the following changes to the database occur as a unit of work. Syntax for BEGIN TRANSACTION BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [ WITH MARK [ "description" ] ] ] If you decide that all is well, you can issue the COMMIT TRAN or COMMIT WORK statement. Syntax for COMMIT TRAN and COMMIT WORK COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ] COMMIT [ WORK ] If you need to undo all of your work, you can issue a ROLLBACK TRAN or ROLLBACK WORK statement. Syntax for ROLLBACK TRAN and ROLLBACK WORK ROLLBACK [ TRAN [ SACTION ] 

[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ] ]

ROLLBACK [ WORK ]</source>


What happens with stored proc transactions and exceptions?

   <source lang="sql">

7> CREATE TABLE SomeData 8> ( 9> SomeColumn INT 10> ) 11> GO 1> 2> --This procedure will insert one row, then throw a divide by zero exception 3> CREATE PROCEDURE NoRollback 4> AS 5> BEGIN 6> INSERT SomeData VALUES (1) 7> 8> INSERT SomeData VALUES (1/0) 9> END 10> GO 1> 2> --Execute the procedure 3> EXEC NoRollback 4> GO (1 rows affected) Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Procedure NoRollback, Line 8 Divide by zero error encountered. 1> 2> --Select the rows from the table 3> SELECT * 4> FROM SomeData 5> GO SomeColumn 

         1

(1 rows affected) 1> 2> drop PROCEDURE NoRollback; 3> GO 1> 2> drop table SomeData; 3> GO 1></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Transaction/TRANSACTION&oldid=6896»