Solved

First In First Out updates in MultiUser

Posted on 2013-01-29
4
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

763 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