About table lock in sql server

suraj Bhoir

9y1.3k

Hi guys,

Created webapplication backend runing sql server -2014

I would like to know if my table is in processing mode, how will I come to know my table has entered into lock mode?

Suppose the table is in lock mode how will I release or remove that particular lock using coding in vb.net or c#.net or sql

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manikandan Murugesan9y ago
Refer the following link : http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
and This link too : https://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes
0
Manish Kumar9y ago
Identify query that are making lock on table.Run sql profiler to trace those query.
Are you doing DML operation like update/delete on heavy records in one batch
0
suraj Bhoir9y ago
Yes sir,
i have used with(nolock) comment also all tables but problem is that,
When my web application is running but that time PC will go in hang process or direct shut down that time application is off but sql backend some prosess is going on so some tables went to in lock prosess.

when pc start and login that application it goes to timeout and through backend manually if relese lock from that table then working properlly but i want to do that remove lock process through programme not manually please help me sir /mam.
0
Ravi Kumar9y ago
To chekc whick tables are locked you can use following query.
SELECT OBJECT_NAME(p.OBJECT_ID) AS TableName, resource_type, resource_description
FROM sys.dm_tran_locks AS l
JOIN sys.partitions AS p
ON l.resource_associated_entity_id = p.hobt_id
TO chek on specific table.
USE DatabaseName;
GO
SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = OBJECT_ID(N'dbo.TableName');
To unlock the table you can use NOLOCK on table.
0