Friday, March 23, 2012

Lock identifying through SP code

We would have come across locks on rows, tables etc. How do we handle it
through code (SP)!!!

A job is executed at 12 pm midnight on a table. This table exists on an
24X7 accessed (users will be access the table anytime in the day/night)
database. We need to execute this job if this table is not locked at 12 pm.
Can we have the solution for the listed are two scenarios:

1. You have a normal user and do not have system privilege.
How would you design the database(Table)? Is it possible?
2. You have all the privileges on the system tables.
What would be the sql script to help us identify the lock? How will
it be used in your code? Or is there exception handling !!!You can see if a lock exists in syslockinfo table:

if exists (select 1 from master.dbo.syslockinfo (nolock) where rsc_objid=object_id('dbo.yourtable') print 'Someone is using the table'
else begin
print 'Table is not in use'
begin tran
select top 1 [lockit]=1 from dbo.yourtable
--do your thing
if @.@.error != 0 rollback tran
else commit tran
end

No comments:

Post a Comment