basilhs_s
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
why don't you use identity columns?
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
begin tran
select @max = (select max(....)) with(holdlock)
insert into ... values (@max + 1....)
end tran