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.
...However, I still get the same error message like the one without transaction...
I am unable to recreate your problem
select * into t1 from sysobjects
create proc p1 as
lock table t1 in exclusive mode
select db_name(dbid), object_name(id,dbid) from master..syslocks
drop procedure p1
drop table t1
(217 rows affected)
(return status = 0)
(1 row affected)