[thinguyen]

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

[svelasco]

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

[thinguyen]

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

[svelasco]

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