?
Solved

sql server 2000 , record locking

Posted on 2009-04-21
5
Medium Priority
?
224 Views
Last Modified: 2013-12-26
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
Comment
Question by:basilhs_s
  • 3
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24192484
do this:

begin tran
select @max = (select max(....)) with(holdlock)
insert into ... values (@max + 1....)
end tran
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 750 total points
ID: 24192488
sorry, ment

begin tran
select @max = (select max(....)) with(updlock)
insert into ... values (@max + 1....)
end tran
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24192493
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
 

Author Comment

by:basilhs_s
ID: 24192643
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
 
LVL 18

Assisted Solution

by:diasroshan
diasroshan earned 750 total points
ID: 24194766
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question