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

Answer 1

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

Answer 2

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

Answer 3

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...

Answer 4

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

Answer 5

Thanks john, i will try that. i will alter the lock and see what happens in  next days!

thanks