Create table dbo.test ( id int, valore int )Immaginiamo che un'applicazione apra una connessione e la popoli usando una transazione con isolation level serializable
Set transaction Isolation level serializable begin transaction MyTestTransaction ;with CTE as(Select 1 riga union all Select 1), CTE4 as(Select A.riga from CTE A, CTE B), CTE16 as(Select A.riga from CTE4 A, CTE4 B), CTE256 as(Select A.riga from CTE16 A, CTE16 B), CTE65536 as(Select A.riga from CTE256 A, CTE256 B) Insert Into Test Select riga,riga from CTE65536 commitDa una seconda connessione cerchiamo le transazioni attive utilizzando le dm view e fn , sys.dm_exec_connections , sys.dm_exec_sessions , sys.dm_tran_session_transactions , sys.dm_tran_database_transactions , sys.dm_exec_sql_text
Select C.session_id ,T.text Command ,case transaction_isolation_level When 0 then 'Unspecified' When 1 then 'Read Uncomitted' When 2 then 'Read Committed' When 3 then 'Repeatable Read' When 4 then 'Serializable' When 5 then 'Snapshot' end as [Transaction Isolation Level] ,S.status ,S.host_name ,S.login_name ,DT.database_transaction_begin_time ,S.last_request_start_time ,S.last_request_end_time from sys.dm_exec_connections C inner join sys.dm_exec_sessions S On C.session_id = S.session_id inner join sys.dm_tran_session_transactions ST On ST.session_id = C.session_id inner Join sys.dm_tran_database_transactions DT on Dt.transaction_id = ST.transaction_id cross apply sys.dm_exec_sql_text(C.most_recent_sql_handle) TEd ecco cosa otteniamo
Ciao
Luca
Nessun commento:
Posta un commento