Check if a Table in Sql Server is locked

Question

I 'm writing a windows application which connects to sql server and performs a update action on a table.

Already there is another program named P2 which is altering the data in same table. So the table is locked

I am getting an error while accessing the table from my application

I need a solution in which the program first checks if the table is already locked and if the table is not locked the data has to be updated. If the table is already locked then it has to wait for a while and retry the operation.

Can anyone Provide me one??

Check the link http://stackoverflow.com/questions/599453/find-locked-table-in-sql-server — Vipin Nair, Jul 18 '12 at 07:45
I did not get the answer i needed tn that link so i asked the question in my way! — Krishna Thota, Jul 18 '12 at 08:21

score 3 · Accepted Answer · answered Jul 18 '12 at 08:01

3

It is never a good idea to check if something is locked or not before performing an operation. Because a lock can be acquired just after you perform the check and just before you perform your update:

if(noLockOnTheTable)
{
  // ... something else acquires the lock just at this point
  updateTable();
}

So it's futile to try to check the lock. You should go ahead and try to acquire the lock instead and perform your update right away. You could be avoiding that because the other operation takes too long and you don't want user to wait. In that case you could specify a "short lock wait timeout" and provide user a message saying that "try again later". User doesn't need to wait.

answered Jul 18 '12 at 08:01

Sedat Kapanoglu

46,641
25
114
148

I got your point. But i 'm getting an error while the update command is being performed if already the table is being used – Krishna Thota Jul 18 '12 at 08:19
1

yes that's expected. you should handle that error (try/catch) and treat it as you like. – Sedat Kapanoglu Jul 18 '12 at 08:50
is there any other way to solve it because the application hangs the computer after few hours of running – Krishna Thota Aug 01 '12 at 05:36
"Application hanging the computer" has nothing to do with the approach I proposed, or SQL calls. That's something entirely different and likely caused by something else (hardware/driver problem). – Sedat Kapanoglu Aug 01 '12 at 07:24

score 2 · Answer 2 · answered Jul 18 '12 at 07:48

2

You could try the following:

CREATE TABLE #lockTable
(
[spid] smallint
, [dbid] smallint
, [objid] int
, [indid] smallint
, [type] nchar(4)
, [resource] nchar(32)
, [mode] nvarchar(8)
, [status] nvarchar(5)
);

INSERT INTO #lockTable EXEC sp_lock;

SELECT * FROM #lockTable WHERE objeid = OBJECT_ID('mytable');

DROP TABLE #lockTable;

answered Jul 18 '12 at 07:48

SchmitzIT

9,227
9
65
92

what i exactly need is how to check a particular table has a lock on it or not. Thank you – Krishna Thota Jul 18 '12 at 08:16

score 0 · Answer 3 · answered Jul 18 '12 at 08:05

For example- get locks in current session:

create table dbo.test (i int)
Go    

begin tran
insert into dbo.test With (tablock) (i) values (1)
Go
Select 
    DB_NAME(tl.resource_database_id) database_name, 
    tl.resource_type, 
    case when resource_type = 'OBJECT' then OBJECT_NAME(tl.resource_associated_entity_id) Else null End ObjectName, 
    tl.resource_type, 
    tl.request_mode,
    tl.request_status,
    tl.* 
From sys.dm_tran_locks tl
Where request_session_id = @@SPID 
order by case when resource_type = 'OBJECT' then OBJECT_NAME(tl.resource_associated_entity_id) Else null End,
    tl.request_mode, tl.resource_type
Rollback

what i exactly need is how to check a particular table has a lock on it or not.By using the result i will perform the remaining tasks. Thank you — Krishna Thota, Jul 18 '12 at 08:15

Check if a Table in Sql Server is locked

3 Answers3