November 5, 2002 at 4:18 pm
We are building an Application where we have a table that has four fields in it. We want to use this table as a 'Queue' table where some proceeses put records in this table and some other processes pick up these records, delete them from the table and process them.
One of the fields in this table is of type datetime. This field tells us the time after which the record can be read (picked up for processing).
Now records are inserted into this table all the time by different processes. At the same time multiple processes (50 threads ) try to read the record that has in it's time field the least time less than getdate(). After reading the process deletes that record so no other process reads the same record for processing.
I have a stored proc for inserting records and another one that selects the record with the least time less than getdate() I read the record and then delete it within a transaction.
We keep getting timeout errors on writes. Seems that the read procs as they are hitting the database all the time don't give the INSERT's much time.
Initially in the read/delete proc I had a exclusive lock, now I have no locks as I moved to updateing the row by putting a bit flag in the row, yet our processes are timing out.
What would be a good design that would enable many writes and read/deletes to take place without making processes time out?
Thanks for your help.
November 5, 2002 at 5:49 pm
Can I see an example of both the SPs? Also, is this on a seperate drive from log files and other data files, and what type of an Array is an array of drives. Are there indexes on it, especially a clustered index? And finally, can you give me a bit more how all this works as far as what the 50 threads do, transactions a second, things like that, plus the process itself?
November 6, 2002 at 2:55 pm
Keep in mind that doing a SELECT inside a transaction still does not put an exclusive lock on that row. You must do something that involves a logged operation (INSERT, UPDATE, DELETE...) before you can lock the row OR you must use the WITH (TABLOCKX) or WITH (XLOCK PAGLOCK) locking hints when you do the SELECT. This will prevent anyone from even reading the table until you commit/rollback the transaction.
If you have more than one process reading the table, then you can have problems with the same row being grabbed twice:
Process A: BEGIN TRAN
Process A: SELECT next row (ID 1)
Process B: BEGIN TRAN
Process B: SELECT next row (!!!also got ID 1)
Process A: DELETE row ID 1
Process B: DELETE row ID 1 (!!!blocked by Process A)
Process A: COMMIT TRAN
Process B: Block is removed by commit above, delete happens, but no row left!!!
Process B: COMMIT TRAN
-Dan
-Dan
November 6, 2002 at 3:41 pm
-- The Table1 has four fields 'Id' unique identifier, 'Body' varchar field with some text, 'XTime' date time field specifies
the time after which the record can be picked up'deleted' flag that tells other processes that the record has been taken.
CREATE PROCEDURE dbo.XYZ
as
SET NOCOUNT ON
declare @Id int
set rowcount 1 -- want to just pick one row at a time
select @Id = [ID]
from Table1
where XTime<GetDate() and deleted = 0
-- pick the first of the eligible records even if multiple threads pick the same id,
-- only the first would be able to send it back and do anything with it. The others would cycle out with o, ' ' selected.
BEGIN TRANSACTION
UPDATE Table1 set deleted = 1
WHERE [Id] = @id and deleted = 0
IF @@ROWCOUNT = 1
BEGIN
SELECT [Id], Body
FROM Table1
WHERE [Id] = @id
DELETE
FROM Table1
WHERE [Id] = @id
END
ELSE
SELECT 0 as 'Id', ' ' as 'Body'
COMMIT TRANSACTION
SET ROWCOUNT 0
GO
This way I do not have to lock anyone at the time SELECTS are done.
The Insert proc is :
CREATE PROCEDURE dbo.INSERTPROC (@Body as varchar(100), @XTime as datetime)
AS
SET NOCOUNT ON
INSERT INTO Table1(Body,XTime) VALUES(@Body,@XTime) -- Id is a field that is an identity field so does not have to be inserted.
This table does not have too many writes,maybe a few 100 every minute, although the read/delete process keeps happening and threads from .net middle tier app written in c# keeps hitting the database even if there is nothing to read.
We do not have this table on a different disk primarily because this table does not have that much data in it.
Also, I had earlier written the read/delete proc with a XLOCK, PAGLOCK earlier but thought this approach that I have in the proc above may lead to less contention.
Thanks for the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply