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.
Nội dung trích xuất từ tài liệu:
Bài giảng Microsoft SQL server: Bài 11 - TS. Lê Thị Tú Kiên Lecture 11 How to manage transactions and lockingMurachs SQL Server 2012, C17 © 2012, Mike Murach & Associates, Inc. Slide 1ObjectivesApplied Givenasetofstatementstobecombinedintoatransaction,insert theTransactSQLstatementstoexplicitlybegin,commit,androll backthetransaction.Knowledge Describetheuseofimplicittransactions. Describetheuseofexplicittransactions. DescribetheuseoftheCOMMITTRANstatementandthe @@TRANCOUNTfunctionwithinnestedtransactions. Describetheuseofsavepoints. Definethesetypesofconcurrencyproblems:lostupdates,dirty reads,nonrepeatablereads,andphantomreads.MurachsSQL ©2012,MikeMurach& Slide 2Server2012,C17 Associates,Inc.Objectives (cont.) Describethewaylockingandthetransactionisolationlevelhelp topreventconcurrencyproblems. DescribethewaySQLServermanageslockingintermsof granularity,lockescalation,sharedlocks,exclusivelocks,and lockpromotion. DescribedeadlocksandthewaySQLServerhandlesthem. Describefourcodingtechniquesthatcanreducedeadlocks.MurachsSQL ©2012,MikeMurach& Slide 3Server2012,C17 Associates,Inc.The SQL script that creates the AP databaseCREATE DATABASE AP;GOUSE 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);MurachsSQL ©2012,MikeMurach&Server2012,C11 Associates,Inc. Slide4The SQL script (cont.)CREATE TABLE Vendors(VendorID INT NOT NULL IDENTITYPRIMARY 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));MurachsSQL ©2012,MikeMurach&Server2012,C11 Associates,Inc. Slide5The SQL script (cont.)CREATE TABLE Invoices(InvoiceID INT NOT NULL IDENTITYPRIMARY 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);MurachsSQL ©2012,MikeMurach&Server2012,C11 Associates,Inc. Slide6The 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));MurachsSQL ©2012,MikeMurach&Server2012,C11 Associates,Inc. Slide71. TransactionsINSERT 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);MurachsSQL ©2012,MikeMurach& Slide 8Server2012,C17 Associates,Inc.The same statements coded as a transactionDECLARE @InvoiceID i ...