Thread: Lock identifying through SP code
02-23-05, 06:41 #1Registered User
- Join Date
- Dec 2004
Unanswered: 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 !!!
02-23-05, 10:36 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
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'
print 'Table is not in use'
select top 1 [lockit]=1 from dbo.yourtable
--do your thing
if @@error != 0 rollback tran
else commit tran
end"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."