one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1
an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.
Can I do this ?
select max(grp) from orv WITH HOLDLOCK
what should I use to avoid lock when I need to insert into orv. ?
Can you explain what you are trying to do from a business or "real world" perspective? It sounds to me like you've dropped into the technology so far that you've lost sight of the original goal, and are making this a lot harder than it needs to be!