• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

sql server 2000 , record locking

i having a table ftrans with the following layout

trans_no           int,
trans_date        datetime,
trans_amount   decimal   (etc)

when i am going to insert a new row in this table i am doing the following steps?
A. Locate the maximum value of trans_no (select isnull(trans_no,0) from ftrans) lets call it max_no
B. Add 1 to max_no
C. insert into ftrans ....fields ....  values .....

how can i do an exclusive lock to table ftrans (with minimum rows affected) in order to be sure that this process will work ok in a multi-user environment?(this means that if two users are trying to insert data at the same time one should wait for the other to complete the three steps above).Do i have to do anything in step A to deal with the fact that select statement there should wait if the record (or table perhaps) is already locked?.

from your experience is it better to use another table to retrieve the last value of trans_no instead of  using the ftrans directly? we could from example using a different table f.e ftrans_num with one field in it (lets call it last_num).ftrans_num will contain one row only. every time we would like to insert a new record to ftrans we could select the value of last_num,add 1 to it ,do the step c and rewrite ftrans_num. in this case if we have to lock a table ,it would be ftrans_num that it should be locked and not ftrans. this means that all programs that use ftrans (f.e reports, statistics etc) would be executed without locking problems

i am using sql server 2000
0
basilhs_s
Asked:
basilhs_s
  • 3
2 Solutions
 
momi_sabagCommented:
do this:

begin tran
select @max = (select max(....)) with(holdlock)
insert into ... values (@max + 1....)
end tran
0
 
momi_sabagCommented:
sorry, ment

begin tran
select @max = (select max(....)) with(updlock)
insert into ... values (@max + 1....)
end tran
0
 
momi_sabagCommented:
you can move it to a different table, but then the bottle neck would be that other table

why don't you use identity columns?
0
 
basilhs_sAuthor Commented:
i cant use identity columns because trans_no is not simply a number. it depends from other fields too
Two users should never get the same @max at any time. therefore the statement select @max = (select max(....)) should lock the record processed even in read mode. there is also a case that must be considered. if ftrans has no rows the statement select @max =...... could not lock anything since no row is retrieved.

the idea of another table has the flexibilty that it will affect the data entry program only. reports should be unaffected
0
 
diasroshanCommented:
Hi,

I would suggest using another table.
I have written a sample function.
U can save the below contents as f_get_transno.srf and import the function.

Hope this helps,

Cheers,
Rosh
$PBExportHeader$f_get_transno.srf
$PBExportComments$Sybase - Function
global type f_get_transno from function_object
end type
 
forward prototypes
global function long f_get_transno ()
end prototypes
 
global function long f_get_transno ();Long ll_transno = 0 
 
//LOCKING SELECTION TO OTHER USERS BY EXECUTING A UPDATE COMMAND
	UPDATE  FTRANS_COUNTER    
	SET	COMPANY  = COMPANY
	WHERE  (COMPANY  = :COMP)
	 AND	(BRANCH  = :BRANCH);
	
	SELECT TRANS_NO INTO :ll_transno
	FROM  FTRANS_COUNTER 
	WHERE	 (COMPANY  = :COMP)
	 AND	(BRANCH  = :BRANCH);
	 
IF SQLCA.SQLCode <> 0 THEN 
	INSERT INTO  FTRANS_COUNTER    
			( COMPANY , 
			BRANCH , 
			TRANS_NO )  
	 VALUES 		( :COMP , 
			:BRANCH , 
			0 )  ;
END IF 
 
IF IsNull(ll_transno) THEN ll_transno = 0 
ll_transno ++ 
 
	
	UPDATE  FTRANS_COUNTER    
	SET	TRANS_NO = :ll_transno 
	WHERE  (COMPANY  = :COMP)
	 AND	(BRANCH  = :BRANCH) ;
 
return ll_transno 
end function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now