StudyClues Student Learning System
In multiuser environments, sql server ensures the integrity of the data by setting write locks on all the tables(Locked tables) when you are updating. This prevents other users from making changes to the same tables.
if the table is locked we cannot do any CRUD(insert/select/update/delete) operations on table until it is unlocked
so if we want find locked tables in sql server we can use below query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT LOCKS.REQUEST_SESSION_ID AS SESSIONID, OBJ.NAME AS LOCKEDOBJECTNAME, DATEDIFF(SECOND,ACTTRA.TRANSACTION_BEGIN_TIME, GETDATE()) AS DURATION, ACTTRA.TRANSACTION_BEGIN_TIME, COUNT(*) AS LOCKS FROM SYS.DM_TRAN_LOCKS LOCKS JOIN SYS.PARTITIONS PARTI ON PARTI.HOBT_ID = LOCKS.RESOURCE_ASSOCIATED_ENTITY_ID JOIN SYS.OBJECTS OBJ ON OBJ.OBJECT_ID = PARTI.OBJECT_ID JOIN SYS.DM_EXEC_SESSIONS EXESESS ON EXESESS.SESSION_ID = LOCKS.REQUEST_SESSION_ID JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TRANSESS ON EXESESS.SESSION_ID = TRANSESS.SESSION_ID JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS ACTTRA ON TRANSESS.TRANSACTION_ID = ACTTRA.TRANSACTION_ID WHERE RESOURCE_DATABASE_ID = DB_ID() AND OBJ.TYPE = 'U' GROUP BY ACTTRA.TRANSACTION_BEGIN_TIME,LOCKS.REQUEST_SESSION_ID, OBJ.NAME |
if we want to kill session use below query then automatically tables will be unlocked
|
1 |
kill [session_id] |
Conclusion:
We hope you find the tip as helpful, I hope you liked the article. Please share your valuable suggestions and feedback in the comments
