Should I use table locking?

21 Jul 2005 10:10 PM

John Bonds

I have a table called BatchIteration (DDL below). There is a IsProcessing
bit field in this table. There will be multiple computers running multiple
threads processing batches. What I need is a query that sets the
IsProcessing bit to true of the "TOP 1" row and at the same time selects the
row. This must be done in a manner that makes certain that other computers
don't fetch the same row and start processing it. Any ideas?

My table looks basically like this:

CREATE TABLE xpaperdev.dbo.BatchIteration
(BatchID INTEGER NOT NULL,
IterationID INTEGER NOT NULL,
IsProcessing BIT NOT NULL,
DateProcessed DATETIME NULL,
CONSTRAINT BatchIterationPK PRIMARY KEY (BatchID, IterationID),
CONSTRAINT Batch_BatchIterationFK FOREIGN KEY (BatchID) REFERENCES Batch
(BatchID))

Thanks for the help!

John

22 Jul 2005 1:42 AM

David Gugick

John Bonds wrote:
Show quote

> I have a table called BatchIteration (DDL below). There is a
> IsProcessing bit field in this table. There will be multiple
> computers running multiple threads processing batches. What I need is
> a query that sets the IsProcessing bit to true of the "TOP 1" row and
> at the same time selects the row. This must be done in a manner that
> makes certain that other computers don't fetch the same row and start
> processing it. Any ideas?
>
> My table looks basically like this:
>
> CREATE TABLE xpaperdev.dbo.BatchIteration
> (BatchID INTEGER NOT NULL,
> IterationID INTEGER NOT NULL,
> IsProcessing BIT NOT NULL,
> DateProcessed DATETIME NULL,
> CONSTRAINT BatchIterationPK PRIMARY KEY (BatchID, IterationID),
> CONSTRAINT Batch_BatchIterationFK FOREIGN KEY (BatchID) REFERENCES
> Batch (BatchID))
>
> Thanks for the help!
>
> John

You can use something like the following without worrying aboutthe
processing flag in the table if this going to be done from a batch: (I
used the same table name and there's a DROP in there, so be careful):

SET NOCOUNT ON

CREATE TABLE dbo.BatchIteration
(BatchID INTEGER NOT NULL,
IterationID INTEGER NOT NULL,
DateProcessed DATETIME NULL,
CONSTRAINT BatchIterationPK PRIMARY KEY (BatchID, IterationID))
Go

Declare @i int
Declare @j int
Set @i = 1
Set @j = 1

While (@i <= 100) Begin
While (@j <= 10) Begin
    Insert into dbo.BatchIteration values (@i, @j, null)
    Set @j = @j + 1
End
Set @i = @i + 1
Set @j = 1
End
Go

SET NOCOUNT ON
Declare @BatchID INT
Declare @IterationID INT
Declare @b BIT

SET @b = 1

While @b = 1 Begin
Begin Tran
Select TOP 1
    @BatchID = BatchID,
    @IterationID = IterationID
From
    dbo.BatchIteration WITH (UPDLOCK, HOLDLOCK)
Where
    DateProcessed IS NULL

If @@ROWCOUNT = 0 Begin
    Set @b = 0
    ROLLBACK
End
Else Begin
    Print 'Batch:' + CAST(@BatchID AS VARCHAR(5)) + ' Iteration: ' +
CAST(@IterationID AS VARCHAR(5))
    Update dbo.BatchIteration
    Set
      DateProcessed = GETDATE()
    Where
      BatchID = @BatchID
    and
      IterationID = @IterationID
    COMMIT TRAN
End
End
GO

DROP TABLE dbo.BatchIteration

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

22 Jul 2005 1:58 AM

Brian Selzer

Try this:

DECLARE @_ERROR INT, @_ROWCOUNT INT
DECLARE @BatchID INT, @IterationID INT

UPDATE BatchIteration
    SET IsProcessing = 1,
            @BatchID = t.BatchID,
            @IterationID = t.IterationID
    FROM
            (SELECT TOP 1 BatchID, IterationID
                FROM BatchIteration
                WHERE IsProcessing = 0) t
    WHERE BatchIteration.BatchID = t.BatchID
            AND BatchIteration.IterationID = t.IterationID
SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT

IF @_ERROR != 0 GOTO ERROR

IF @_ROWCOUNT > 0
    SELECT ...
        FROM BatchIteration
        WHERE BatchID = @BatchID
                AND IterationID = @IterationID

RETURN 0
ERROR: -- do error handling here

Show quote

"John Bonds" <johnbo***@hotmail.com> wrote in message
news:#$Z43DkjFHA.3336@tk2msftngp13.phx.gbl...
> I have a table called BatchIteration (DDL below). There is a IsProcessing
> bit field in this table. There will be multiple computers running multiple
> threads processing batches. What I need is a query that sets the
> IsProcessing bit to true of the "TOP 1" row and at the same time selects
the
> row. This must be done in a manner that makes certain that other computers
> don't fetch the same row and start processing it. Any ideas?
>
> My table looks basically like this:
>
> CREATE TABLE xpaperdev.dbo.BatchIteration
> (BatchID INTEGER NOT NULL,
> IterationID INTEGER NOT NULL,
> IsProcessing BIT NOT NULL,
> DateProcessed DATETIME NULL,
> CONSTRAINT BatchIterationPK PRIMARY KEY (BatchID, IterationID),
> CONSTRAINT Batch_BatchIterationFK FOREIGN KEY (BatchID) REFERENCES Batch
> (BatchID))
>
> Thanks for the help!
>
> John
>
>

22 Jul 2005 2:13 AM

Brian Selzer

The where clause may need to look like this:

     WHERE BatchIteration.BatchID = t.BatchID
             AND BatchIteration.IterationID = t.IterationID
             AND IsProcessing = 0

If anything, it won't hurt to put it in.

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:OKhXbDmjFHA.1148@TK2MSFTNGP12.phx.gbl...
> Try this:
>
> DECLARE @_ERROR INT, @_ROWCOUNT INT
> DECLARE @BatchID INT, @IterationID INT
>
> UPDATE BatchIteration
>     SET IsProcessing = 1,
>             @BatchID = t.BatchID,
>             @IterationID = t.IterationID
>     FROM
>             (SELECT TOP 1 BatchID, IterationID
>                 FROM BatchIteration
>                 WHERE IsProcessing = 0) t
>     WHERE BatchIteration.BatchID = t.BatchID
>             AND BatchIteration.IterationID = t.IterationID
> SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
>
> IF @_ERROR != 0 GOTO ERROR
>
> IF @_ROWCOUNT > 0
>     SELECT ...
>         FROM BatchIteration
>         WHERE BatchID = @BatchID
>                 AND IterationID = @IterationID
>
> RETURN 0
> ERROR: -- do error handling here
>
>
>
>
> "John Bonds" <johnbo***@hotmail.com> wrote in message
> news:#$Z43DkjFHA.3336@tk2msftngp13.phx.gbl...
> > I have a table called BatchIteration (DDL below). There is a
IsProcessing
> > bit field in this table. There will be multiple computers running
multiple
> > threads processing batches. What I need is a query that sets the
> > IsProcessing bit to true of the "TOP 1" row and at the same time selects
> the
> > row. This must be done in a manner that makes certain that other
computers
> > don't fetch the same row and start processing it. Any ideas?
> >
> > My table looks basically like this:
> >
> > CREATE TABLE xpaperdev.dbo.BatchIteration
> > (BatchID INTEGER NOT NULL,
> > IterationID INTEGER NOT NULL,
> > IsProcessing BIT NOT NULL,
> > DateProcessed DATETIME NULL,
> > CONSTRAINT BatchIterationPK PRIMARY KEY (BatchID, IterationID),
> > CONSTRAINT Batch_BatchIterationFK FOREIGN KEY (BatchID) REFERENCES
Batch
> > (BatchID))
> >
> > Thanks for the help!
> >
> > John
> >
> >
>
>

26 Jul 2005 11:48 PM

John Bonds

Thanks all

The update solution worked. I didn't realize that I could also change the
values of output variables with an update command.

John

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:eu1VrLmjFHA.3608@TK2MSFTNGP12.phx.gbl...
> The where clause may need to look like this:
>
>      WHERE BatchIteration.BatchID = t.BatchID
>              AND BatchIteration.IterationID = t.IterationID
>              AND IsProcessing = 0
>
> If anything, it won't hurt to put it in.
>
> "Brian Selzer" <br***@selzer-software.com> wrote in message
> news:OKhXbDmjFHA.1148@TK2MSFTNGP12.phx.gbl...
> > Try this:
> >
> > DECLARE @_ERROR INT, @_ROWCOUNT INT
> > DECLARE @BatchID INT, @IterationID INT
> >
> > UPDATE BatchIteration
> >     SET IsProcessing = 1,
> >             @BatchID = t.BatchID,
> >             @IterationID = t.IterationID
> >     FROM
> >             (SELECT TOP 1 BatchID, IterationID
> >                 FROM BatchIteration
> >                 WHERE IsProcessing = 0) t
> >     WHERE BatchIteration.BatchID = t.BatchID
> >             AND BatchIteration.IterationID = t.IterationID
> > SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
> >
> > IF @_ERROR != 0 GOTO ERROR
> >
> > IF @_ROWCOUNT > 0
> >     SELECT ...
> >         FROM BatchIteration
> >         WHERE BatchID = @BatchID
> >                 AND IterationID = @IterationID
> >
> > RETURN 0
> > ERROR: -- do error handling here
> >
> >
> >
> >
> > "John Bonds" <johnbo***@hotmail.com> wrote in message
> > news:#$Z43DkjFHA.3336@tk2msftngp13.phx.gbl...
> > > I have a table called BatchIteration (DDL below). There is a
> IsProcessing
> > > bit field in this table. There will be multiple computers running
> multiple
> > > threads processing batches. What I need is a query that sets the
> > > IsProcessing bit to true of the "TOP 1" row and at the same time
selects
> > the
> > > row. This must be done in a manner that makes certain that other
> computers
> > > don't fetch the same row and start processing it. Any ideas?
> > >
> > > My table looks basically like this:
> > >
> > > CREATE TABLE xpaperdev.dbo.BatchIteration
> > > (BatchID INTEGER NOT NULL,
> > > IterationID INTEGER NOT NULL,
> > > IsProcessing BIT NOT NULL,
> > > DateProcessed DATETIME NULL,
> > > CONSTRAINT BatchIterationPK PRIMARY KEY (BatchID, IterationID),
> > > CONSTRAINT Batch_BatchIterationFK FOREIGN KEY (BatchID) REFERENCES
> Batch
> > > (BatchID))
> > >
> > > Thanks for the help!
> > >
> > > John
> > >
> > >
> >
> >
>
>