?
Solved

First In First Out updates in MultiUser

Posted on 2013-01-29
4
Medium Priority
?
248 Views
Last Modified: 2013-02-06
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
0
Comment
Question by:rxf65609
  • 2
4 Comments
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38833889
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
 

Author Comment

by:rxf65609
ID: 38835336
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38835846
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38861735
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

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question