Problem selecting unique MAX

We have a stored procedure that 'selects max(nvl(fieldM,0),value)+1 from table where fieldA=123 and fieldB = 456;'. This value is then inserted back into the table into FieldM as a record fieldA=123, fieldb=456, fieldM=Maxvalue, and another IDfield. After about a couple 100 of these it is over, and the stored procedure will be concerned with a new fieldA, fieldB etc and the max value for them will begin at 1 and increase for awhile. The problems is that many, many users can be calling that stored procedure at the same time, and two different users can get the same max value + 1 and insert it back into the table thus creating duplicates, yet the value has to be unique. How can I get the max value currently for my criteria and insert it back as a new record without having someone else creating a duplicate using the same procedure? This all happens pretty fast.

Answer Wiki

Thanks. We'll let you know when a new response is added.

My background is in Oracle, but I believe that most database systems operate the same locking principle…

Try a single UPDATE statement: update my_table set FieldM = nvl( FieldM, 0 ) + 1 where FieldA=123 and FieldB=456.

If the WHERE clause identifies a single row, then this is straightforward. The row is locked, so other processes doing the same UPDATE can’t read it until the change has been committed (because it’s an UPDATE, not a SELECT).

If the WHERE clause doesn’t identify a single row, the implication is that you would be updating a column in ALL the rows to the same value. Of course, once this has worked once (ie all the FieldM values are the same) then the UPDATE statement above would work fine – it’s just that it would lock More than one row. There’s no need for the MAX function because all the rows identified by the WHERE clause will keep the same value for FieldM. The trick is to lock everyone out of the table while you get all the values for FieldM set correctly for that first time.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • JLeask
    If you are using Oracle - look at SEQUENCES, first create your sequence e.g. create sequence fieldM_seq;. Then it can be used in SQL... INSERT INTO T1 (fieldM, fieldA) VALUES (fieldM_seq.nextval, 123); This handles the increments, the only problem is there may be gaps in the sequence due to application rollback etc but it should solve your duplication problem.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: