Shared - Locks in SQL Server - Part 3

Veröffentlicht am: 20 April 2017
auf dem Kanal: IT Port

1,410

Click here to Subscribe to IT PORT Channel : / @itport28

Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

Isolation Level - • SQL Server Isolation Example

Script for Active_Locks Function
---------------------------------------------------

Create Function Active_locks () returns table return
select Top 10000000 case dtl.request_session_id
when -2 then 'orphaned distributed transaction'
when -3 then 'deferred recovery transaction'
else dtl.request_session_id end as spid,
db_name(dtl.resource_database_id) as databasename,
so.name as lockedobjectname, dtl.resource_type as lockedresource,
dtl.request_mode as locktype, es.login_name as loginname, es.host_name as hostname,
case tst.is_user_transaction when 0 then 'system transaction'
when 1 then 'user transaction' end as user_or_system_transaction,
at.name as transactionname, dtl.request_status
from sys.dm_tran_locks dtl join sys.partitions sp on sp.hobt_id = dtl.resource_associated_entity_id
join sys.objects so on so.object_id = sp.object_id
join sys.dm_exec_sessions es on es.session_id = dtl.request_session_id
join sys.dm_tran_session_transactions tst on es.session_id = tst.session_id
join sys.dm_tran_active_transactions at on tst.transaction_id = at.transaction_id
join sys.dm_exec_connections ec on ec.session_id = es.session_id
cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) as st
where resource_database_id = db_id() order by dtl.request_session_id

Auf dieser Seite können Sie das Online-Video Shared - Locks in SQL Server - Part 3 mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer IT Port 20 April 2017 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 1,410 Mal angesehen und es wurde von 6 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!

13,624

228