Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    47

    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 !!!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •