Best way to lock data for my scenario

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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=#########".

Answer 5

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