August 9, 2013 at 1:40 am
IF update command is run on a table and commit is pending then another user cannot be able to update the table.
Is there any query to get the user or PC which caused lock on the table.
August 9, 2013 at 1:59 am
You can try the usual method of SP_WHO2 and SP_Lock if you are around and able to query while the locking is happening. Otherwise you can try something like the following
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P
ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O
ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES
ON ES.session_id = L.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 CN
ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id() ORDER BY L.request_session_id
(This is borrowed from somewhere I can't remember where)
Otherwise you will need to look at using Server side tracing and extended events if it is occuring while there is no user monitoring.
August 9, 2013 at 1:59 am
sys.dm_exec_requests is the DMV with this information. There's a blocking session ID column. If you want something pre-packaged and easy to use in the future, I'd recommend sp_whoisactive.
Read committed snapshot (or any other isolation level) won't stop two updates from blocking each other if they touch the same data.
August 9, 2013 at 4:38 am
HowardW (8/9/2013)
sys.dm_exec_requests is the DMV with this information. There's a blocking session ID column. If you want something pre-packaged and easy to use in the future, I'd recommend sp_whoisactive.Read committed snapshot (or any other isolation level) won't stop two updates from blocking each other if they touch the same data.
How to transactions can update same data? Does the first one will lock that record during update?
August 9, 2013 at 4:49 am
IT researcher (8/9/2013)
HowardW (8/9/2013)
sys.dm_exec_requests is the DMV with this information. There's a blocking session ID column. If you want something pre-packaged and easy to use in the future, I'd recommend sp_whoisactive.Read committed snapshot (or any other isolation level) won't stop two updates from blocking each other if they touch the same data.
How to transactions can update same data? Does the first one will lock that record during update?
Yes, once rows, pages, extents or tables are locked by an update, anything else that updates them prior to the first update being committed will be blocked.
All Read Committed Snapshot isolation does is allow readers to read the previously committed data without being blocked by writers and not take shared locks for reads so that writers are also not blocked. Writers will always block each other, they have to.
Also remember that an update is not necessarily just taking row locks, they'll often take page locks, which lock multiple rows.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply