locked
Multiple threads or JVMS processing the same table and must not read the same row( must read distinct rows) RRS feed

  • Question

  • This is the problem I have. We have applications hosted on multiple JVMs reading a table on a single SQL server instance. They will all run a query like get the top most unprocessed row from the table. These threads should not dead lock and get a row every time. The rows are not updated, only read. Is there a way to implement this?. Do I need to create a control table with the keys from the main table and let the threads just update the key of the row they picked and then go to the main table. DB is not my area of expertise. Any help on this is much appreciated. TY
    Tuesday, August 23, 2011 5:55 AM

Answers

  • One other approach is to serialize read process with sp_getapplock/sp_releaseapplock stored procedures. It will work similarly to the critical sections on the client side.

    One of the things you need to account for is what to do if your jvms thread crashed. So you can have the code like that. (Again, just to give you some ideas)

    create table dbo.Table1
    (
    	ID int not null,
    	Col1 int,
    	ProcessingTimeOut datetime not null -- when row had been selected for processing
    		constraint DEF_Table1_ProcessingTimeOut
    		default '2000-01-01',
    		
    	constraint PK_Table1
    	primary key clustered(ID)
    )
    go
    
    create proc dbo.ReadData
    as
    begin
    	set xact_abort on
    	
    	declare
    		@T table (
    			ID int not null, -- primary key
    			Col1 int -- all columns
    		)
    
    	declare
    		@Result int
    		,@ProcessingTimeOut datetime
    				
    	begin tran
    		
    		exec @Result = sp_getapplock @Resource='ReadData', @LockMode = 'Exclusive';
    		
    		if @Result >= 0
    			select @ProcessingTimeOut = DATEADD(minute,-1,getutcdate())
    			
    		insert into @T(ID, Col1)
    				select ID, Col1
    				from dbo.Table1
    				where ProcessingTimeOut <= @ProcessingTimeOut
    		
    		update dbo.Table1
    		set
    			ProcessingTimeOut = GETUTCDATE()
    		where
    			ID in (select ID from @T)
    	commit
    	
    	select ID, Col1 from @T
    end
    			
    



    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, August 23, 2011 11:53 PM
  • It seems your intent is to implement some sort of queue table.  In that case, take a look at SQL Server Service Broker feature.  This will allow you to do so without polling the database.  See http://technet.microsoft.com/en-us/library/ms166104.aspx.  There is some learning curve but it is very powerful and worth the effort.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Wednesday, August 24, 2011 12:38 AM
    Answerer

All replies

  • Hi redfernolive,

    This is the problem I have. We have applications hosted on multiple JVMs reading a table on a single SQL server instance. They will all run a query like get the top most unprocessed row from the table. These threads should not dead lock and get a row every time. The rows are not updated, only read. Is there a way to implement this?. Do I need to create a control table with the keys from the main table and let the threads just update the key of the row they picked and then go to the main table. DB is not my area of expertise. Any help on this is much appreciated. TY

    If the rows are not updated, then how do you mark them as processed
    once they are done? It is possible to block access for others while a
    transaction is running, but once a transaction finishes, the locks
    will be lifted and if the row is not updated, there's no way to see
    that it is now processed.

    However, to get what you want, look into the READPAST and UPDLOCK
    locking hints.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, August 23, 2011 11:32 PM
  • One other approach is to serialize read process with sp_getapplock/sp_releaseapplock stored procedures. It will work similarly to the critical sections on the client side.

    One of the things you need to account for is what to do if your jvms thread crashed. So you can have the code like that. (Again, just to give you some ideas)

    create table dbo.Table1
    (
    	ID int not null,
    	Col1 int,
    	ProcessingTimeOut datetime not null -- when row had been selected for processing
    		constraint DEF_Table1_ProcessingTimeOut
    		default '2000-01-01',
    		
    	constraint PK_Table1
    	primary key clustered(ID)
    )
    go
    
    create proc dbo.ReadData
    as
    begin
    	set xact_abort on
    	
    	declare
    		@T table (
    			ID int not null, -- primary key
    			Col1 int -- all columns
    		)
    
    	declare
    		@Result int
    		,@ProcessingTimeOut datetime
    				
    	begin tran
    		
    		exec @Result = sp_getapplock @Resource='ReadData', @LockMode = 'Exclusive';
    		
    		if @Result >= 0
    			select @ProcessingTimeOut = DATEADD(minute,-1,getutcdate())
    			
    		insert into @T(ID, Col1)
    				select ID, Col1
    				from dbo.Table1
    				where ProcessingTimeOut <= @ProcessingTimeOut
    		
    		update dbo.Table1
    		set
    			ProcessingTimeOut = GETUTCDATE()
    		where
    			ID in (select ID from @T)
    	commit
    	
    	select ID, Col1 from @T
    end
    			
    



    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, August 23, 2011 11:53 PM
  • It seems your intent is to implement some sort of queue table.  In that case, take a look at SQL Server Service Broker feature.  This will allow you to do so without polling the database.  See http://technet.microsoft.com/en-us/library/ms166104.aspx.  There is some learning curve but it is very powerful and worth the effort.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Wednesday, August 24, 2011 12:38 AM
    Answerer