Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    32

    Unanswered: Lock Table concept

    Hi All
    I was trying to modify a stored procedure. The procedure has a lock table statement
    Code:
    lock table User in exclusive mode
    After I execute the procedure, I get the following error message
    A LOCK TABLE command is not allowed outside the scope of a transaction on table 'User' in the database 'User1'

    If I remove the lock table, everything seems to work fine.

    Does anyone know why I get the error message? and is it going to be a problem if I remove the lock table statement?

    Thanks in advance for your help

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need a begin transaction or
    Maybe it should run in chained transaction mode in which case Adaptive Server implicitly invokes a begin transaction. But you must still explicitly close the transaction with a commit.

    Using lock table can prevent running out of locks.

    Also useful when an immediate table lock may reduce the overhead of acquiring a large number of row or page locks and save overall locking time. e.g.
    When a table will be scanned more than once in the same transaction, and each scan may need to acquire many page or row locks
    when a scan will exceed a table's lock-promotion threshold and will therefore attempt to escalate to a table lock in any event.

  3. #3
    Join Date
    Dec 2007
    Posts
    32
    Thanks pdreyer for the explanation about lock table
    adding the begin transaction and commit transaction solves my problem

  4. #4
    Join Date
    Dec 2007
    Posts
    32
    Hi pdreyer and everyone
    I was wondering is it possible to use the stored procedure with lock table statement without transaction statement IN the stored procedure but outside the stored procedure.

    For example, I have a stored procedure(LockTable.proc) with the lock table statement
    I have another stored procedure(Caller.proc) which calls the LockTable.proc.
    In my Caller.proc, I try the following

    Code:
    begin transaction newTrans
    declare @output char(10)
    exec LockTable @output output
    commit transaction newTrans

    However, I still get the same error message like the one without transaction.
    I am just wondering is there a way to resolve this problem?

    Is placing transaction statement inside the LockTable.proc the only way to resolve the problem?

    Thanks in advance for your help

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by tiger66
    ...However, I still get the same error message like the one without transaction...
    I am unable to recreate your problem
    Code:
    select * into t1 from sysobjects
    go
    create proc p1 as 
    lock table t1 in exclusive mode
    go
    begin transaction
    exec p1
    select db_name(dbid), object_name(id,dbid)  from master..syslocks
    commit
    go
    drop procedure p1
    drop table t1
    go
                                                                  
    -                              -                              
    tempdb                         t1                             
    
    (217 rows affected)
    (return status = 0)
    (1 row affected)

  6. #6
    Join Date
    Dec 2007
    Posts
    32
    Thanks so much pdreyer !
    It works now

Posting Permissions

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