Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 327

SELECT orderid, productid, unitprice, qty, discount 

FROM Sales.OrderDetails -- WITH (READCOMMITTEDLOCK) 

WHERE orderid = 10249;

1-3

Run the following code in Connection 3 and identify the locks and process IDs involved in the block-

ing chain.

SELECT -- use * to explore 

  request_session_id            AS spid, 

  resource_type                 AS restype, 

  resource_database_id          AS dbid, 

  resource_description          AS res, 

  resource_associated_entity_id AS resid, 

  request_mode                  AS mode, 

  request_status                AS status 

FROM sys.dm_tran_locks;

1-4

Replace the process IDs 52 and 53 with the ones you found to be involved in the blocking chain in 

the previous exercise. Run the following code to obtain connection, session, and blocking information 

about the processes involved in the blocking chain.

-- Connection info: 

SELECT -- use * to explore 

  session_id AS spid, 

  connect_time, 

  last_read, 

  last_write, 

  most_recent_sql_handle 

FROM sys.dm_exec_connections 

WHERE session_id IN(52, 53); 

 

-- Session info 

SELECT -- use * to explore 

  session_id AS spid, 

  login_time, 

  host_name, 

  program_name, 

  login_name, 

  nt_user_name, 

  last_request_start_time, 

  last_request_end_time 

FROM sys.dm_exec_sessions 

WHERE session_id IN(52, 53); 

 

www.it-ebooks.info

---

328  

Microsoft SQL Server 2012 T-SQL Fundamentals

-- Blocking 

SELECT -- use * to explore 

  session_id AS spid, 

  blocking_session_id, 

  command, 

  sql_handle, 

  database_id, 

  wait_type, 

  wait_time, 

  wait_resource 

FROM sys.dm_exec_requests 

WHERE blocking_session_id > 0;

1-5

Run the following code to obtain the SQL text of the connections involved in the blocking chain.

SELECT session_id, text  

FROM sys.dm_exec_connections 

  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST  

WHERE session_id IN(52, 53);

1-6

Run the following code in Connection 1 to roll back the transaction.

ROLLBACK TRAN;

Observe in Connection 2 that the SELECT query returned the two order detail rows, and that those 

rows were not modified.

Remember that if you need to terminate the blocker’s transaction, you can use the KILL command. 

Close all connections.

Exercises 2-1 through 2-6 deal with isolation levels.