Sybase ASE lock table to disallow users insert values

Question

I need to disallow users to insert values in some table, for example for a 5 mins. Seems I should use at isolation clause but can't figure out how to use it. Thanks

Answer 1

What operation(s) are you trying to perform on the table? Some DML? Maintenance (eg, reorg, (re)indexing, adding/changing RI)? Modifying DDL?

If just doing DML, consider:

begin tran
lock table <table_name> in exclusive mode
--perform DML--
commit (rollback?) tran

'lock table/exclusive mode' (attempts to obtain an exclusive table-level lock), once completed (may have to wait for other locks to be released before successfully obtaining the exclusive lock), means only your current session has access to the table (minus anyone using dirty reads).

Since the 'lock table' command is transactional in nature you need to wrap it inside a transaction hence the 'begin/commit tran'. [If not wrapped in the 'begin/commit tran' then the command would still obtain the exclusive table lock but then immediately release it.]

If you're looking to perform maintenance and/or DDL operations ... there's no easy way to limit access short of denying/revoking access to the table, database or dataserver.