Increment Table2 (D, E) D ED1 E1D2 E2 Performing the following statement in the first connection Begin TRAN UPDATE TABLE1 SET A = 'aa' where b = 'b2' waitfor delay '00: 00: 30 'update table2 set d = 'D5' WHERE E = 'E1' Commit TRAN Performs the following statement in the second connection Begin TRAN UPDATE TABLE2 SET D = 'D5' WHERE E = 'E1' WAITFOR DELAY '00: 00: 10 'Update Table1 Set A = 'aa' where b = 'b2' commit tran
At the same time, the system will detect the deadlock and abort the process - set TB (a, b, c) Create Table #TB (a varchar (2), b varchar (2), c varchar (2)) Insert Into # TB SELECT 'A1', 'B1', 'C1' Union All Slect 'A2', 'B2', 'C2' Union All Select 'A3', 'B3', 'C3'
--1) Rowed lock - Perform the following statement in the first connection Begin TRAN UPDATE #TB set a = 'aa' where b = 'b2' waitfor delay '00: 00: 3 '- Wait 3 seconds COMMIT TRAN
- Perform the following statement Begin Tran Select * from #tb where b = 'b2' commom #TB where b = 'b2' commit tran - If the above two statements are simultaneously executed, the SELECT query must wait for the UPDATE to be executed. second
--2) Shared lock - Perform the following statement in the first connection Begin Tran Select * from #tb Holdlock - Holdlock artificial lock WHERE B = 'b2' waitfor delay '00: 00: 3 '- Waiting 3 Seconds Commit TRAN
- Execute the following statement in the second connection Begin Tran SELECT A, C from #tb set a = 'aa' where b = 'b2' commit tran - If the above two The SELECT query in the second connection can be executed in the second connection - and the Update must wait for 30 seconds after the end of the shared lock in the first connection.
--3) Dead lock - add TB2 (D, E) Create Table # TB2 (D varchar (2), e varchar (2)) Insert Into # TB2 SELECT 'D1', 'E1' Union All SELECT 'D2' , 'E2'
- Perform the following statement in the first connection Begin TRAN UPDATE #TB set a = 'aa' where b = 'b2' waitfor delay '00: 00: 5 'update # TB2 set d =' d5 'where e =' E1 'Commit Tran - Performing the following statement in the second connection Begin TRAN UPDATE # TB2 SET D =' D5 'WHERE E =' E1 'WAITFOR DELAY '00: 00: 3' Update #tb set a = 'aa' WHERE B = 'b2' commit tran - delete temporary table Drop Table # TB, # TB2
- Also executed, the system will detect dead locks and aborted processes / * ----------------------------------------------------------------------------------------------------------------------------- --------------------------- Set Implicit_Transactions on - users must explicitly submit or roll back each time. Otherwise, when the user is disconnected, - transaction and all data changes included in it will roll back
SET IMPLICIT_TRANSACTION OFF - Automatic Submit mode. In automatic submission mode, if each statement is successful - complete it. -------------------------------------------------- ---------------------- * / -
- Lock records, only allow examples of single-user modification:
- Create a test environment - create a test table - department table Create Table department (DepartmentID INT, Name Varchar (10))
- Record Lock Table Create Table Lock (DepartmentID INT, DT DATIME)
Go - because getDate in the function, so use a view, get the current time CREATE VIEW V_GETDATE AS SELECT DT = getDate () Go - Create a custom function, determine if the record is locked to lock create function f_chk (@DEPARTMENTID INT) Returns bitasbegindeclare @re bit, @ dt datetimeselect @ dt = dt from v_getdateif exists (select 1 from lock where departmentid = @ departmentid and datediff (ss, dt, @ dt) <5) set @ re = 1else set @ re = 0return (@ RE) ENDGO
- Data Processing IF DBO.F_CHK (3) = 1 Print 'Record Locked' Elsebegin Begin Tran Insert Into Lock Values (3, Getdate ()) Update Site Set Name = 'a' Where DepartmentID = 3 Delete from Lock Where DepartmentID = 3 Commit Tranend
- Delete Test Environment DROP TABLE Department DROP View V_GETDATEDROP FUNCTION F_CHK
