ch13.sql

SQLServer2005基础教程_配套课件和脚本

CREATE TABLE employees(
emp_ID INT, 
emp_Name VARCHAR(20))
INSERT INTO dbo.employees(emp_ID, emp_Name) VALUES(1, 'Henry')



USE ETravelCom
BEGIN TRANSACTION
INSERT INTO dbo.employees(emp_ID, emp_Name) VALUES(1, 'Henry')
SAVE TRANSACTION a
INSERT INTO dbo.employees(emp_ID, emp_Name) VALUES(2, 'Josef')
SAVE TRANSACTION b
INSERT INTO dbo.employees(emp_ID, emp_Name) VALUES(3, 'Michael')
ROLLBACK TRANSACTION b
INSERT INTO dbo.employees(emp_ID, emp_Name) VALUES(4, 'Rudolf')
ROLLBACK TRANSACTION a
COMMIT TRANSACTION
SELECT *
FROM employees

USE ETravelCom
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

DELETE FROM employees
DROP TABLE employees


sp_lock '54'

SELECT STasks.session_id, SThreads.os_thread_id     
    FROM sys.dm_os_tasks AS STasks         
    INNER JOIN sys.dm_os_threads AS SThreads             
        ON STasks.worker_address = SThreads.worker_address     
    WHERE STasks.session_id IS NOT NULL     
    ORDER BY STasks.session_id;
GO

SELECT *
    FROM sys.dm_tran_locks 



CREATE TABLE t_lock 
    (
    c1 int, c2 int
    );
GO

CREATE INDEX t_lock_ci on t_lock(c1);
GO

-- Insert values into test table.
INSERT INTO t_lock VALUES (1, 1);
INSERT INTO t_lock VALUES (2,2);
INSERT INTO t_lock VALUES (3,3);
INSERT INTO t_lock VALUES (4,4);
INSERT INTO t_lock VALUES (5,5);
INSERT INTO t_lock VALUES (6,6);
GO

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN
    SELECT c1 
        FROM t_lock 
        WITH(holdlock, rowlock);

-- Session 2
BEGIN TRAN
    UPDATE t_lock SET c1 = 10

select * from master.sys.databases

-- This query will display lock information. The 
-- value for <dbid> should be replaced with the
-- database_id from sys.databases.
SELECT resource_type, resource_associated_entity_id, 
    request_status, request_mode,request_session_id, 
    resource_description 
    FROM sys.dm_tran_locks
    WHERE resource_database_id = 

--  This query returns object information using 
--  resource_associated_entity_id from the previous
--  query.
SELECT object_name(object_id), * 
    FROM sys.partitions 
    WHERE hobt_id=<resource_associated_entity_id>

-- This query will show blocking information.
SELECT resource_type, resource_database_id, 
    resource_associated_entity_id, request_mode, 
    request_session_id, blocking_session_id 
    FROM sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2
    WHERE t1.lock_owner_address = t2.resource_address

-- Release the resources by rolling back the transactions
-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO