yanci1179
asked on
SQL 2000 Table Lock
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/selecti
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) ?
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/selecti
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) ?
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"
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"
ASKER
how would adding a flag prevent a table lock? Also, I am not deleting all the records, only a portion.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
partially complete answer
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..