Bài giảng Microsoft SQL server: Bài 11 - TS. Lê Thị Tú Kiên

Chia sẻ: Việt Cường Nguyễn | Ngày: | Loại File: PPTX | Số trang:35

Thêm vào BST

Báo xấu

28
lượt xem 3
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

"Bài giảng Microsoft SQL server - Bài 11: Quản lý giao tác và khóa" trình bày một tập hợp các câu lệnh được kết hợp thành một giao dịch, hãy chèn các câu lệnh Transact-SQL để bắt đầu, cam kết và khôi phục giao dịch một cách rõ ràng.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Microsoft SQL server: Bài 11 - TS. Lê Thị Tú Kiên

Lecture 11 How to manage transactions and locking Murach's SQL Server 2012, C17 © 2012, Mike Murach & Associates, Inc. Slide 1
Objectives Applied Given a set of statements to be combined into a transaction, insert the TransactSQL statements to explicitly begin, commit, and roll back the transaction. Knowledge Describe the use of implicit transactions. Describe the use of explicit transactions. Describe the use of the COMMIT TRAN statement and the @@TRANCOUNT function within nested transactions. Describe the use of save points. Define these types of concurrency problems: lost updates, dirty reads, nonrepeatable reads, and phantom reads. Murach's SQL © 2012, Mike Murach & Slide 2 Server 2012, C17 Associates, Inc.
Objectives (cont.) Describe the way locking and the transaction isolation level help to prevent concurrency problems. Describe the way SQL Server manages locking in terms of granularity, lock escalation, shared locks, exclusive locks, and lock promotion. Describe deadlocks and the way SQL Server handles them. Describe four coding techniques that can reduce deadlocks. Murach's SQL © 2012, Mike Murach & Slide 3 Server 2012, C17 Associates, Inc.
The SQL script that creates the AP database CREATE DATABASE AP; GO USE AP; CREATE TABLE Terms (TermsID INT NOT NULL PRIMARY KEY, TermsDescription VARCHAR(50) NOT NULL, TermsDueDays SMALLINT NOT NULL); CREATE TABLE GLAccounts (AccountNo INT NOT NULL PRIMARY KEY, AccountDescription VARCHAR(50) NOT NULL); Murach's SQL © 2012, Mike Murach & Server 2012, C11 Associates, Inc. Slide 4
The SQL script (cont.) CREATE TABLE Vendors (VendorID INT NOT NULL IDENTITY PRIMARY KEY, VendorName VARCHAR(50) NOT NULL, VendorAddress1 VARCHAR(50) NULL, VendorAddress2 VARCHAR(50) SPARSE NULL, VendorCity VARCHAR(50) NOT NULL, VendorState CHAR(2) NOT NULL, VendorZipCode VARCHAR(20) NOT NULL, VendorPhone VARCHAR(50) NULL, VendorContactLName VARCHAR(50) NULL, VendorContactFName VARCHAR(50) NULL, DefaultTermsID INT NOT NULL REFERENCES Terms(TermsID), DefaultAccountNo INT NOT NULL REFERENCES GLAccounts(AccountNo)); Murach's SQL © 2012, Mike Murach & Server 2012, C11 Associates, Inc. Slide 5
The SQL script (cont.) CREATE TABLE Invoices (InvoiceID INT NOT NULL IDENTITY PRIMARY KEY, VendorID INT NOT NULL REFERENCES Vendors(VendorID), InvoiceNumber VARCHAR(50) NOT NULL, InvoiceDate SMALLDATETIME NOT NULL, InvoiceTotal MONEY NOT NULL, PaymentTotal MONEY NOT NULL DEFAULT 0, CreditTotal MONEY NOT NULL DEFAULT 0, TermsID INT NOT NULL REFERENCES Terms(TermsID), InvoiceDueDate SMALLDATETIME NOT NULL, PaymentDate SMALLDATETIME NULL); Murach's SQL © 2012, Mike Murach & Server 2012, C11 Associates, Inc. Slide 6
The SQL script (cont.) CREATE TABLE InvoiceLineItems (InvoiceID INT NOT NULL REFERENCES Invoices(InvoiceID), InvoiceSequence SMALLINT NOT NULL, AccountNo INT NOT NULL REFERENCES GLAccounts(AccountNo), InvoiceLineItemAmount MONEY NOT NULL, InvoiceLineItemDescription VARCHAR(100) NOT NULL, PRIMARY KEY (InvoiceID, InvoiceSequence)); Murach's SQL © 2012, Mike Murach & Server 2012, C11 Associates, Inc. Slide 7
1. Transactions INSERT statements that work with related data DECLARE @InvoiceID int; INSERT Invoices VALUES (34,'ZXA-080','2012-04-30',14092.59, 0,0,3,'2012-05-30',NULL); SET @InvoiceID = @@IDENTITY; INSERT InvoiceLineItems VALUES (@InvoiceID,1,160,4447.23,'HW upgrade'); INSERT InvoiceLineItems VALUES (@InvoiceID,2,167,9645.36,'OS upgrade'); Murach's SQL © 2012, Mike Murach & Slide 8 Server 2012, C17 Associates, Inc.
The same statements coded as a transaction DECLARE @InvoiceID int; BEGIN TRY BEGIN TRAN; INSERT Invoices VALUES (34,'ZXA-080','2012-04-30',14092.59, 0,0,3,'2012-05-30',NULL); SET @InvoiceID = @@IDENTITY; INSERT InvoiceLineItems VALUES (@InvoiceID,1,160,4447.23,'HW upgrade'); INSERT InvoiceLineItems VALUES (@InvoiceID,2,167,9645.36,'OS upgrade'); COMMIT TRAN; END TRY BEGIN CATCH ROLLBACK TRAN; END CATCH; Murach's SQL © 2012, Mike Murach & Slide 9 Server 2012, C17 Associates, Inc.
When to use explicit transactions When you code two or more action queries that affect related data When you update foreign key references When you move rows from one table to another table When you code a SELECT query followed by an action query and the values inserted in the action query are based on the results of the SELECT query When a failure of any set of SQL statements would violate data integrity Murach's SQL © 2012, Mike Murach & Slide 10 Server 2012, C17 Associates, Inc.
The SQL statements for processing transactions BEGIN {TRAN|TRANSACTION} SAVE {TRAN|TRANSACTION} save_point COMMIT [TRAN|TRANSACTION] ROLLBACK [[TRAN|TRANSACTION] [save_point]] Murach's SQL © 2012, Mike Murach & Slide 11 Server 2012, C17 Associates, Inc.
A script that performs a test before committing the transaction BEGIN TRAN; DELETE Invoices WHERE VendorID = 34; IF @@ROWCOUNT > 1 BEGIN ROLLBACK TRAN; PRINT 'More invoices than expected. ' + 'Deletions rolled back.'; END; ELSE BEGIN COMMIT TRAN; PRINT 'Deletions committed to the database.'; END; The response from the system (3 row(s) affected) More invoices than expected. Deletions rolled back. Murach's SQL © 2012, Mike Murach & Slide 12 Server 2012, C17 Associates, Inc.
How nested transactions work If you commit a transaction when @@TRANCOUNT is equal to 1, all of the changes made to the database during the transaction are committed and @@TRANCOUNT is set to zero. If you commit a transaction when @@TRANCOUNT is greater than 1, @@TRANCOUNT is simply decremented by 1. The ROLLBACK TRAN statement rolls back all active transactions regardless of the nesting level where it’s coded. It also sets the value of @@TRANCOUNT back to 0. Murach's SQL © 2012, Mike Murach & Slide 13 Server 2012, C17 Associates, Inc.
A script with nested transactions BEGIN TRAN; PRINT 'First Tran @@TRANCOUNT: ' + CONVERT(varchar,@@TRANCOUNT); DELETE Invoices; BEGIN TRAN; PRINT 'Second Tran @@TRANCOUNT: ' + CONVERT(varchar,@@TRANCOUNT); DELETE Vendors; COMMIT TRAN; -- This COMMIT decrements @@TRANCOUNT. -- It doesn't commit 'DELETE Vendors'. PRINT 'COMMIT @@TRANCOUNT: ' + CONVERT(varchar,@@TRANCOUNT); ROLLBACK TRAN; PRINT 'ROLLBACK @@TRANCOUNT: ' + CONVERT(varchar,@@TRANCOUNT); Murach's SQL © 2012, Mike Murach & Slide 14 Server 2012, C17 Associates, Inc.
A script with nested transactions (continued) PRINT ' '; DECLARE @VendorsCount int, @InvoicesCount int; SELECT @VendorsCount = COUNT (*) FROM Vendors; SELECT @InvoicesCount = COUNT (*) FROM Invoices; PRINT 'Vendors Count: ' + CONVERT (varchar , @VendorsCount); PRINT 'Invoices Count: ' + CONVERT (varchar , @InvoicesCount); The response from the system First Tran @@TRANCOUNT: 1 (114 row(s) affected) Second Tran @@TRANCOUNT: 2 (122 row(s) affected) COMMIT @@TRANCOUNT: 1 ROLLBACK @@TRANCOUNT: 0 Vendors count: 122 Invoices count: 114 Murach's SQL © 2012, Mike Murach & Slide 15 Server 2012, C17 Associates, Inc.
A transaction with two save points IF OBJECT_ID('tempdb..#VendorCopy') IS NOT NULL DROP TABLE tempdb.. #VendorCopy; SELECT VendorID, VendorName INTO #VendorCopy FROM Vendors WHERE VendorID < 5; BEGIN TRAN; DELETE #VendorCopy WHERE VendorID = 1; SAVE TRAN Vendor1; DELETE #VendorCopy WHERE VendorID = 2; SAVE TRAN Vendor2; DELETE #VendorCopy WHERE VendorID = 3; SELECT * FROM #VendorCopy; ROLLBACK TRAN Vendor2; SELECT * FROM #VendorCopy; ROLLBACK TRAN Vendor1; SELECT * FROM #VendorCopy; COMMIT TRAN; SELECT * FROM #VendorCopy; Murach's SQL © 2012, Mike Murach & Slide 16 Server 2012, C17 Associates, Inc.
The response from the system Murach's SQL © 2012, Mike Murach & Slide 17 Server 2012, C17 Associates, Inc.
Terms Transaction Commit a transaction Roll back a transaction Autocommit mode Save point Murach's SQL © 2012, Mike Murach & Slide 18 Server 2012, C17 Associates, Inc.
2. Concurrency and locking Two transactions that retrieve and then modify the same row Transaction A BEGIN TRAN; DECLARE @InvoiceTotal money, @PaymentTotal money, @CreditTotal money; SELECT @InvoiceTotal = InvoiceTotal, @CreditTotal = CreditTotal, @PaymentTotal = PaymentTotal FROM Invoices WHERE InvoiceID = 112; UPDATE Invoices SET InvoiceTotal = @InvoiceTotal, CreditTotal = @CreditTotal + 317.40, PaymentTotal = @PaymentTotal WHERE InvoiceID = 112; COMMIT TRAN; Murach's SQL © 2012, Mike Murach & Slide 19 Server 2012, C17 Associates, Inc.
Two transactions that retrieve and then modify the same row (continued) Transaction B BEGIN TRAN; DECLARE @InvoiceTotal money, @PaymentTotal money, @CreditTotal money; SELECT @InvoiceTotal = InvoiceTotal, @CreditTotal = CreditTotal, @PaymentTotal = PaymentTotal FROM Invoices WHERE InvoiceID = 112; UPDATE Invoices SET InvoiceTotal = @InvoiceTotal, CreditTotal = @CreditTotal, PaymentTotal = @InvoiceTotal - @CreditTotal, PaymentDate = GetDate() WHERE InvoiceID = 112; COMMIT TRAN; Murach's SQL © 2012, Mike Murach & Slide 20 Server 2012, C17 Associates, Inc.