Best way to lock data for my scenario

Sudip Bhatt 2,271 Reputation points
2020-09-15T09:05:41.87+00:00

Suppose i have a table order where order info stored and i have a console application which is running in 5 pc and this console application will fetch data from table order but in specific way.

suppose if console application running in PC1 fetch those order data which not fetched by other same console application running in other PC. how could i develop this logic as a result if console app1 fetch records 1 to 5 then console application app2 will not fetch records 1 to 5 rather it may fetch available records which is not fetched by other same console application running in other PC.

if my scenario is not clear. then please tell me then i will explain it more elaborately with example.

please give me knowledge to develop this kind of application which will be fetching data from same table but always works with those free data which has not been fetched by other same console application running in other PC.

do i need to maintain any trick at db end or it can be done fully at application level?

do i need to lock db records by select statement and if yes then what isolation level should be followed in my SP for fetching data ?

How do i design my table and how do i lock table as a result there will be no performance hit and each console application can work with free data which is not taken by same console application running on another pc.

thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,309 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,522 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Jeffrey Williams 1,886 Reputation points
    2020-09-15T20:49:31.977+00:00

    You could try using a processed/read column on the table - and updating that column and output the selected data.

    Declare @sourceTable Table (col1 int, col2 int, col3 int, readFlag bit Not Null);  
     Insert Into @sourceTable (col1, col2, col3, readFlag)  
     Values (1, 1, 1, 0)  
          , (2, 2, 2, 0)  
          , (3, 3, 3, 0)  
          , (4, 4, 4, 0)  
          , (5, 5, 5, 0)  
          , (11, 1, 1, 0)  
          , (12, 2, 2, 0)  
          , (13, 3, 3, 0)  
          , (14, 4, 4, 0)  
          , (15, 5, 5, 0)  
          , (21, 1, 1, 0)  
          , (22, 2, 2, 0)  
          , (23, 3, 3, 0)  
          , (24, 4, 4, 0)  
          , (25, 5, 5, 0);  
      
     Select *  
       From @sourceTable st;  
      
     --==== First process calls this...  
    Declare @outputTable Table (col1 int, col2 int, col3 int);  
      
     Update Top (5)  
            @sourceTable  
        Set readFlag = 1  
      Output inserted.col1, inserted.col2, inserted.col3 Into @outputTable  
      Where readFlag = 0  
      
     Select *  
       From @outputTable ot;  
      
     Select *  
       From @sourceTable st;  
      
      
     --==== For testing a second call - we clear the output table and update the next set of rows.  
     --==== this is only for testing and would not be part of the solution  
            Delete From @outputTable;  
      
     Update Top (5)  
            @sourceTable  
        Set readFlag = 1  
      Output inserted.col1, inserted.col2, inserted.col3 Into @outputTable  
      Where readFlag = 0  
      
     Select *  
       From @outputTable ot;  
      
     Select *  
       From @sourceTable st;  
    

    The UPDATE will lock the rows it needs to lock - update the values - then you return the rows that were updated to the client for further processing. The second client will be blocked until the first client is done updating, then select the next 5 rows and return to that client for further processing.

    You should not have any problems with isolation levels as the update will lock the rows - and subsequent updates will need to wait for an update lock.

    1 person found this answer helpful.

  2. Erland Sommarskog 99,296 Reputation points MVP
    2020-09-16T20:58:58.47+00:00

    Here is one more approach which I will only give an outline of. There is actually a built-in component in SQL Server to handle queues like this one, and that is Service Broker. The process that now is intended to write to this table would post messages on a Service Broker queue. The consumer would retrieve rows from that queue with the RECEIEVE statement. You don't have to worry about isolation levels - Service Broker takes care of that for you.

    Service Broker is a very powerful feature in SQL Server when used in the right place. But it comes with a learning curve, and monitoring can be a problem. If there are five consecutive rollbacks in the processing of a message from a queue, the queue is disabled, and this sort of happens silently and it can take a while to figure out.

    If I were in Sudip's application team, I would probably suggest that we should use Service Broker, but if no one in the team is familiar with it, it may be better to stick to a roll-your-own-in-a-table solution. You would not be alone to make that choice.

    1 person found this answer helpful.

  3. Shashank Singh 6,246 Reputation points
    2020-09-15T09:36:55.35+00:00

    I am not a design expert but hear out my idea. I would have a column in the table may be called as "read flag". The data type of this column would be bit. Let us say if you read rows 1-10 the rows corresponding to rows read in column Read Flag would be set to 1. And when next PC would read it would make sure you read data filtered out with something like and where ReadFlag=0.

    ex

    select col1,col2, .... from table_name where id between 10 and 20 and RadFlag=0
    

  4. Tom Phillips 17,711 Reputation points
    2020-09-15T16:19:18.407+00:00

    Locking in that way is not a great thing to do. I would suggest you look at:
    https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrency

    If you must I would look at
    https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15

    You can do something like "@resource=ordernumber=#########".

    0 comments No comments

  5. Tom Phillips 17,711 Reputation points
    2020-09-16T19:43:46.587+00:00

    This will give you an idea of how to use app locks. Run this in 2 different spids.

    BEGIN TRAN;
    
    DECLARE @selectedorders TABLE (OrderId INT)
    
    INSERT INTO @selectedorders(OrderId)
    SELECT TOP 5
        o.OrderID
    FROM dbo.Orders o
    WHERE APPLOCK_TEST('public', 'OrderNumber:'+CAST(o.OrderId AS VARCHAR(10)), 'Exclusive', 'Transaction')=1
    ORDER BY o.OrderID
    
    DECLARE @result int;  
    DECLARE @orderid INT;
    DECLARE @res VARCHAR(255);
    
    
    DECLARE lockrecs CURSOR FOR
        SELECT OrderId FROM @selectedorders;
    OPEN lockrecs
    FETCH NEXT FROM lockrecs INTO @orderid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @res = 'OrderNumber:'+CAST(@orderid AS VARCHAR(10))
        EXEC @result=sp_getapplock  
            @DbPrincipal='public',  
            @Resource=@res,  
            @LockMode='Exclusive',  
            @LockOwner='Transaction'; 
        IF @result < 0 -- Error locking record
        BEGIN
            RAISERROR('Error during lock OrderId: %d',16,1, @orderid)
            RETURN;
        END
        FETCH NEXT FROM lockrecs INTO @orderid
    END
    CLOSE lockrecs;
    DEALLOCATE lockrecs;
    
    
    
    SELECT * FROM @selectedorders;
    
    -- Do stuff here
    WAITFOR DELAY '00:01:00' -- Hold locks for 1 mins
    
    -- Free locks
    DECLARE freerecs CURSOR FOR
        SELECT OrderId FROM @selectedorders;
    OPEN freerecs
    FETCH NEXT FROM freerecs INTO @orderid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @res = 'OrderNumber:'+CAST(@orderid AS VARCHAR(10))
        EXEC @result=sp_releaseapplock  
            @DbPrincipal='public',  
            @Resource=@res,  
            @LockOwner='Transaction'; 
        IF @result < 0 -- Error locking record
        BEGIN
            RAISERROR('Error releasing lock OrderId: %d',16,1, @orderid)
            RETURN;
        END
        FETCH NEXT FROM freerecs INTO @orderid
    END
    CLOSE freerecs;
    DEALLOCATE freerecs;
    
    COMMIT;
    RETURN