If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > remove lock on a table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On