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

"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. | Lecture 11 How to manage transactions and locking Murach s SQL Server 2012 C17 2012 Mike Murach amp Associates Inc. Slide 1 Objectives Applied Given a set of statements to be combined into a transaction insert the Transact SQL 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 amp 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 amp 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 amp 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 .