none
Locks in SQL Server 2008

    Question

  • Hi everyone 

    i've got a problem with locks. i have a VB5 app working with SQL SERVER 2008( last 3 months). this app have 40 users working on it. in last one year i've locks almost every day and mostly at the same time...10am and 6pm. The locks are always on the same table, simple table that have only one row with sequence number. A Stored procedure increments that number. 

    sometimes we got 3 weeks with no locks....after that, they returns every day....sometimes 2 times for day.

    There is a possiblity that the problem can be in domain server? or in other configuration "outside" of sql server? 

    thanks for the help
    Tuesday, July 07, 2009 9:11 PM

Answers

  • As you said, the table only has one record, this means that the records will always have a exclusive lock while updating.
    you may want to change the code to

    update a
        set @res = cod_ana + 1,
              code_ana = code_ana +1,
    from s1_config a (rowlock)

    update lock may give you inconsistancies.




    Thanks

    John Huang http://sqlx.blogspot.com

    Wednesday, July 08, 2009 7:00 AM

All replies

  • This is a very typical scneraio which can potentically create blocking in the Database. If you have found the table which is getting into a lock You need to look at the objects that access the central table this is being used for sequence generation. Make sure you handle the transactions well so that you can avoid locks.

    If the Sequence is not alphanumeric you should use Identity to be very safe. If you give some more details it would be really useful.

    Regards
    Muralidhar

    Tuesday, July 07, 2009 9:27 PM
  • Hi

    thanks for your answer. 
    This is the procedure. The table SL_CONFIG only have one simple row with some columns, each one have a sequence.
    CREATE procedure [dbo].[GERADOR]
    ( 
    	@campo	VarChar(20), 
    	@res	int OUTPUT 
    )
    as
    declare @auxInc int
    
        if @campo = 'N_REQUIS' begin 
    		begin tran
    			select @auxInc = n_requis from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_requis = @auxInc
    		commit tran
    	end
        else if @campo = 'N_REC' begin
    		begin tran
    			select @auxInc = n_rec from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_rec = @auxInc
    		commit tran
    	end
        else if @campo = 'N_DOC_CAIXA' begin
    		begin tran
    			select @auxInc = n_doc_caixa from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_doc_caixa = @auxInc
    		commit tran
    	end
        else if @campo = 'N_SESSAO' begin
    		begin tran
    			select @auxInc = n_sessao from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_sessao = @auxInc
    		commit tran
    	end
        else if @campo = 'N_FOLHA_TRAB' begin
    		begin tran
    			select @auxInc = n_folha_trab from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_folha_trab = @auxInc
    		commit tran
    	end
        else if @campo = 'SEQ_REALIZA' begin
    		begin tran
    			select @auxInc = seq_realiza from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set seq_realiza = @auxInc
    		commit tran
    	end
        else if @campo = 'SEQ_UTENTE' begin
    		begin tran
    			select @auxInc = seq_utente from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set seq_utente = @auxInc
    		commit tran
    	end        
        else if @campo = 'SEQ_FACT'	begin
    		begin tran
    			select @auxInc = seq_fact from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set seq_fact = @auxInc
    		commit tran
    	end
        else if @campo = 'N_REQUIS_CONSULTAS' begin
    		begin tran
    			select @auxInc = n_requis_consultas from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_requis_consultas = @auxInc
    		commit tran
    	end
    
        else if @campo = 'N_ANULACAO' begin
    		begin tran
    			select @auxInc = n_anulacao from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_anulacao = @auxInc
    		commit tran
    	end 	 
        else if @campo = 'N_CAUCAO' begin
    		begin tran
    			select @auxInc = n_caucao from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_caucao = @auxInc
    		commit tran
    	END
    	else if @campo = 'COD_ANA' begin
    		begin tran
    			select @auxInc = cod_ana from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set cod_ana = @auxInc
    		commit tran
    	end 	 
    
        select @res = @auxInc
    
    return
    After i find the locks i run the SP_WHO2 to find who-is-blocking-who and when i kill the process it's in the top of the list, nothing happens, other process pass to top of the list and i the "solution" is restart de sql server service. 

    Thanks for the help

    Tuesday, July 07, 2009 9:47 PM
  • I am able to repro the issue,  if this happnes,

    1. a transaction calls the SP and enters into the first IF statement,

        if @campo = 'N_REQUIS' begin 
    		begin tran
    			select @auxInc = n_requis from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_requis = @auxInc
    		commit tran
    	end
    

    but before the controls hits the COMMIT line, there is the second call,

    2. Another call comes from the application which hits potentially a different IF

        else if @campo = 'N_REC' begin
    		begin tran
    			select @auxInc = n_rec from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_rec = @auxInc
    		commit tran
    	end
    

    remember the last call has ended yet and this call interfers with the previous once.

    Now 2 waits for 1 and in the mean while if there are other calls that hit the same IF condition as in case 1, it gives raise to a chain of blocked transactions. Thus when you try to kill one the next SPID gets blocked.. finally SQL services requires a recycle. But this is not safe as suring re-cycle there could be data loss due to un commited transactions.

    The problem is with the design of the table, one single row and there are multiple columns, instead you should have one row for each Key and store the seq as value; with multiple rows we would be able to narrow down and force rowlock instead of all rows being affected. Currently even when you are trying to update different columns at the same time, since there is only 1 row, the row gets locked (I have given the repro steps in the bottom of the thread) and connections are blocked. However this is just one solution you should consider the efforts as per impact..

    Steps to Create a Block,

    1. Open SSMS and paste the code below,

    declare @auxInc int
    		begin tran
    			select @auxInc = n_requis from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_requis = @auxInc
    

    Run the above script and dont commit or close the window
    2. Open another tab and paste the below code

    declare @auxInc int
    		begin tran
    			select @auxInc = n_rec from sl_config (UPDLOCK)
    			select @auxInc = @auxInc + 1
    			update sl_config set n_rec = @auxInc
    

    Run this script when you have the other also open.. Now 2 should wait for 1; you can also create a bigger chain of blocks by repeating this.



    Regards Muralidhar If my response helped, please help me by marking it as the answer and voting as appropriate...
    • Proposed as answer by r.muralidhar Wednesday, July 08, 2009 3:45 AM
    Wednesday, July 08, 2009 3:38 AM
  • As you said, the table only has one record, this means that the records will always have a exclusive lock while updating.
    you may want to change the code to

    update a
        set @res = cod_ana + 1,
              code_ana = code_ana +1,
    from s1_config a (rowlock)

    update lock may give you inconsistancies.




    Thanks

    John Huang http://sqlx.blogspot.com

    Wednesday, July 08, 2009 7:00 AM
  • Thanks john, i will try that. i will alter the lock and see what happens in  next days!

    thanks

    Wednesday, July 08, 2009 9:14 AM