Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    61

    remove lock on a table

    Hi All,

    I have a problem:

    I want to drop a table but it raise an error: "user dba has a row in test_table locked."
    (test_table: table name)
    How do I remove lock on that table?
    Normally I must restart server and then drop table successfully but I don't want to restart server because I often drop and create that table.

    Thanks in advanced for your help,
    Thi Nguyen

  2. #2
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    Try to see why you have a lock in the table.
    Use:
    sp_lock
    or this select:

    Use <my_database>
    go
    SELECT substring(db_name(master..syslocks.dbid),1,15) db_name,
    object_name(master..syslocks.id)tablename,
    master..syslocks.row,
    master..syslocks.page,
    tipo =
    CASE
    WHEN master..syslocks.type = 1 THEN "Exclusive table lock"
    WHEN master..syslocks.type = 2 THEN "Shared table lock"
    WHEN master..syslocks.type = 3 THEN "Exclusive intent lock"
    WHEN master..syslocks.type = 4 THEN "Shared intent lock"
    WHEN master..syslocks.type = 5 THEN "Exclusive page lock"
    WHEN master..syslocks.type = 6 THEN "Shared page lock"
    WHEN master..syslocks.type = 7 THEN "Update page lock"
    WHEN master..syslocks.type = 8 THEN "Exclusive row lock"
    WHEN master..syslocks.type = 9 THEN "Shared row lock"
    WHEN master..syslocks.type = 10 THEN "Update row lock"
    WHEN master..syslocks.type = 11 THEN "Shared next key lock"
    WHEN master..syslocks.type = 256 THEN "Lock is blocking another process"
    WHEN master..syslocks.type = 512 THEN "Demand lock"
    END,
    master..syslocks.type,
    master..syslocks.class,
    master..syslocks.context,
    master..syslocks.spid,
    master..syslocks.fid,
    master..syslocks.loid
    FROM master..syslocks
    WHERE master..syslocks.dbid = db_id()
    ORDER BY master..syslocks.spid
    go

    If you have a transaction open and you make an insert, update or delete, then you will have to commit or rollback the transaction before drop the table.
    Sometimes Sybase lose locks with phantom process, but that is an exception.
    Intead of drop and create the table, You can try truncate table.
    Bye

    Sebastian

  3. #3
    Join Date
    Jul 2004
    Posts
    61
    Hi,
    Thanks a lot for your help.
    I want to force remove lock on a table because someones still hold their transactions so I can't end their transaction.
    There is a problem here how do I drop a table by any way: remove lock on that table or end any transaction that related to that table is not ended.

    Could you tell me please?

    Thanks
    Thi Nguyen

  4. #4
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    You have to kill the process that hold the lock, if you want to drop the table.
    May be this select will help you:

    sp_lock
    go

    SELECT "kill", syslocks.spid, "-- ", suser_name(sysprocesses.suid)
    FROM master..syslocks syslocks,
    master..sysprocesses sysprocesses
    WHERE syslocks.dbid = db_id()
    AND syslocks.spid = sysprocesses.spid
    AND object_name(syslocks.id) = "MyTable"
    ORDER BY syslocks.spid
    go

    Bye

    Sebastian Velasco

Posting Permissions

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