About table lock in sql server

Question

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

Manikandan Murugesan · Answer

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

Manish Kumar · Answer

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

suraj Bhoir · Answer

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.

Ravi Kumar · Answer

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.