SQL 2000 Table Lock

yanci1179
yanci1179 used Ask the Experts™
on
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?
or
should i use hints like (UPDLOCK, HOLDLOCK) ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> After some research I found that a table lock is occuring.

In order to delete all records in a table, table lock would be held out and that's normal..
Find out which other process is not releasing its lock for this operation to be performed..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
One Alternate approach that can be tried:

1. Create a new column named flag / status in table1.
2. Update the Flag/ status to "Y" to mark those records are to be copied to table2.
3. Do your INSERT into TABLE2 staement like this

insert into table2 ( columns)
select columns from table1 ( with nolock)
where status = 'Y'

4. Delete records from Table1 where status = "Y"

Author

Commented:
how would adding a flag prevent a table lock?  Also, I am not deleting all the records, only a portion.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> how would adding a flag prevent a table lock?  Also, I am not deleting all the records, only a portion.

Because you would be adding a WHERE clause based upon this FLAG and records that falls under this criteria would be locked or impacted..

Earlier by doing this approach we saved a lot of time..

Author

Commented:
I ran a simple test

SELECT 1 colTest INTO dbo.testTable

declare @count AS int
SET @count = 2
WHILE @count <=10000

begin
INSERT INTO  dbo.testTable VALUES (@count)
SET @count = @count + 1
end

In one screen I ran this statement without the commit
BEGIN TRANSACTION

DELETE dbo.testTable
WHERE colTest = 2

In another screen I added statement
Select * from dbo.testTable
WHERE colTest = 3

The select was blocked.  It would not yeild any result until I commited the result in the first query.  Of course if I add (nolock) to the select I get results. however, this is not an option given that to many statements would require to change in our sp's

Author

Commented:
partially complete answer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial