I have a procedure that is in a transaction. the Transaction first
inserts data from table1 into Table2 (an archive table), then deletes the data from table1.
The whole process tables about 2 min. We have users complaining that they get kicked out of an application that uses table1 for updating/inserting/selecting data.
After some research I found that a table lock is occuring. We use to insert and delete the records one row at a time which did not cause table lock. Is there a way to disable table locking and only allow row level locks? So that users can continue to operate on this table? Then when the transaction finishes enable table locking?
Is SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE a good option?
should i use hints like (UPDLOCK, HOLDLOCK) ?