i having a table ftrans with the following layout
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