Solved

First In First Out updates in MultiUser

Posted on 2013-01-29
4
204 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:
ScottPletcher 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:ScottPletcher
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 65
SQL Error in WHERE Clause 5 39
How to use left join to take all data from master table? 11 42
SQL Query Syntax Join 4 32
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now