Solved

First In First Out updates in MultiUser

Posted on 2013-01-29
4
213 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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

770 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