SQL Server/T-SQL Tutorial/Transaction/TRANSACTION
Содержание
- 1 A script with nested transactions
- 2 A transaction is bound by the ACID test. ACID stands for Atomicity, Consistency, Isolation (or Independence), and Durability:
- 3 BEGIN TRANSACTION
- 4 Declare variable in a transaction
- 5 Exception before transaction committing
- 6 Explicit Transaction Commands
- 7 Forcing an exclusive table lock.
- 8 Inserting a Row into MyTable and Rolling Back the Transaction
- 9 One batch that contains two transactions
- 10 statements coded as a transaction
- 11 Transaction spread across batches:
- 12 Using Explicit Transactions
- 13 Using Transactions
- 14 What happens with stored proc transactions and exceptions?
A script with nested transactions
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>
A transaction is bound by the ACID test. ACID stands for Atomicity, Consistency, Isolation (or Independence), and Durability:
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
BEGIN TRANSACTION
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
Declare variable in a transaction
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
Exception before transaction committing
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
Explicit Transaction Commands
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
Forcing an exclusive table lock.
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
Inserting a Row into MyTable and Rolling Back the Transaction
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>
One batch that contains two transactions
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>
statements coded as a transaction
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>
Transaction spread across batches:
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
Using Explicit Transactions
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>
Using Transactions
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 ]
What happens with stored proc transactions and exceptions?
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>