First In First Out updates in MultiUser

I have an SQL database that contains a table similar to the one listed below.  I have users logging into a web page, I need to be able to have the first user get the first record, the second user gets the next record and so on.  After the first user completes entering data for the first record the first users would then get the next available (next record that another user is not currently looking at) record.  The [InspectBatchKeyId] field contains a number that goes from smallest to largest (smaller number are the first in). I am using Linq to SQL to select and update the data.



CREATE TABLE [dbo].[InspectImage](
      [ImageId] [int] IDENTITY(1,1) NOT NULL,
      [QABatchID] [int] NULL,
      [BatchId] [nvarchar](50) NOT NULL,
      [ImageName] [nvarchar](60) NOT NULL,
      [ImageLocation] [nvarchar](max) NOT NULL,
      [ImagePlateId] [nvarchar](50) NULL,
      [ImagePlateIdDuplicate] [nvarchar](50) NULL,
      [ImageStateId] [nchar](15) NULL,
      [ImageRejectCode] [nvarchar](50) NULL,
      [ImageStatus] [nchar](15) NOT NULL,
      [InsertedBy] [nvarchar](50) NULL,
      [InsertedDate] [datetime] NULL,
      [ReviewedBy] [nvarchar](50) NULL,
      [ModifiedBy] [nvarchar](50) NULL,
      [ModifiedDate] [datetime] NULL,
      [Version] [int] NOT NULL,
      [Locked] [bit] NULL,
      [InReview] [bit] NULL,
      [Prefix] [int] NULL,
      [XCord] [nvarchar](25) NULL,
      [YCord] [nvarchar](25) NULL,
      [Oops] [bit] NULL,
      [AdminReview] [bit] NULL,
      [RowVersion] [timestamp] NULL,
      [InspectBatchId] [nvarchar](50) NULL,
      [PRIDEReturnFile] [nvarchar](255) NULL,
      [FileReconciled] [bit] NULL,
      [ImageStatusId] [int] NULL,
      [InspectBatchKeyId] [bigint] NULL,
      [SentToFDOTDateTime] [datetime] NULL,
      [QADateTime] [datetime] NULL,
      [SetXYUser] [nvarchar](50) NULL,
      [SetXYDate] [datetime] NULL,
      [ClosedInSetXY] [bit] NULL,
      [SentToFDOTDate] [datetime] NULL,
      [UserSessionId] [nvarchar](38) NULL,
      [ImageWidth] [int] NULL,
      [ImageHeight] [int] NULL
rxf65609Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Two choices:

1) Do the UPDATE *before* the SELECT, but still using locks on the SELECT w/i the UPDATE statement (should have least chance of conflicts between users or deadlocks when reading, assuming the SELECT TOP (1) is not slow):
 
UPDATE [dbo].[InspectImage]
SET
    <assigned_id> = <user_id>
WHERE
    [ImageId] = (
        SELECT TOP (1) ImageId
        FROM [dbo].[InspectImage] WITH (ROWLOCK, XLOCK)
        WHERE
            <assigned_id> = 0
    )



2) When you do the read that gets a user a row, do it as part of a transaction and lock the row when you first SELECT it:

BEGIN TRANSACTION
DECLARE @ImageId int
SELECT TOP (1) @ImageId = ImageId
FROM [dbo].[InspectImage] WITH (ROWLOCK, XLOCK)
WHERE
    <assigned_id> = 0
UPDATE [dbo].[InspectImage]
SET
    <assigned_id> = <user_id>
WHERE
    ImageId = @ImageId
COMMIT



If on SQL 2008 (or later), I suggest creating a filtered index on the assigned flag, for best performance.
0
 
deviprasadgCommented:
Take the MIN of what ever InspectImage that is unassigned and assign it to the user:

PseudoCode after the user logs into the webpage:
-Fetch the MIN of InspectImage that is unassigned.(based on InspectBatchKeyId)
- Mark the specific record as assigned and assign it to the user

More on MIN can be found here: http://msdn.microsoft.com/en-us/library/ms179916(v=sql.105).aspx
0
 
rxf65609Author Commented:
Thank you for the update.  I am doing something similar to what you are suggesting.  Instead of using MIN I am using TOP(1).  The issue seems to be that several users are making the request at the same time.  The same record is being returned to multiple users.  On the web server I am updating the record to assigned to one user.  The problem is that multiple users already have the record on their screen.  I need a way to always select a unique record that is not being updated by another user.
0
 
Scott PletcherSenior DBACommented:
Sorry, forget to mention one other thing:

In option #1, if you need more data from the row that got UPDATEd, you can use the OUTPUT clause; for example:


UPDATE [dbo].[InspectImage]
SET
    <assigned_id> = <user_id>
OUTPUT
    INSERTED.<assigned_id>, INSERTED.<other_column> INTO @predefined_table_variable
WHERE
    [ImageId] = (
        SELECT TOP (1) ImageId
        FROM [dbo].[InspectImage] WITH (ROWLOCK, XLOCK)
        WHERE
            <assigned_id> = 0
    )

Then you can read the table variable to get the details for the row just assigned.
0
All Courses

From novice to tech pro — start learning today.