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

  • Locking a table for 5mins will block other transactions. What are you trying to achieve by exclusively locking a table for 5 mins ? – Kin Shah Dec 2 '14 at 22:32

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.