1

Let's say I have a sale order with multiple rows of products. Multiple users insert rows so I must check for concurrency. In my db the ID of the order and the number of the row makes my record unique.

In the past I've used one of the following two methods:

  1. Insert a blank record with default value,the max number of row available and then update the record.
  2. Check if the RowNumber already exists. If it doesn't, insert otherwise prompt the user (Usually just insert with the next available row number).

What are other methods I can use ?

  • 1
    This seems like a solved problem. Have you considered using an Object-Relational Mapper or Active Record implementation? As James Snell points out below, most databases already cater for this. – Robert Harvey Apr 28 '16 at 16:17
  • "the ID of the order and the number of the row makes my record unique" so the same order can have multiple records with different tor IDs? – Tulains Córdova Apr 29 '16 at 17:18
  • I only have an ID for the order, than the rows have a number starting from 1 that I use also for ordering the rows. – SilentRage47 May 1 '16 at 0:21
3

The simplest method to use to avoid the situation is probably not to do it at all.

Most databases will take care of it for you and automatically generate a row-id without you having to lift a finger.

1

Your second method is probably the best approach. However, you didn't ask what the "best approach" was or mention any specific problem you are encountering outside of concurrency, but rather:

What are other methods I can use ?

"Row number" for an order is not really a necessity for uniqueness in your database. When an order is displayed, the row order may or may not matter. If you abstract out the concept of a "row number" in your table, you get more options:

  1. Row number of the table (not the sale) would guarantee uniqueness while allowing your RDBMS to manage concurrency in most cases (like AUTOINCREMENT on MySQL). If you need a "row number" you could write code to assign the number after the insert - and then return the results.

  2. Add another key (like userId) to your key structure. Might be helpful for audit purposes, but mostly two users can't have the same ID, so it would guarantee that even if the "row number" was identical, the key is not. However, then a sale may have duplicate "row numbers", so they really aren't "row numbers" but rather the "sequence that a user entered products for this sale." Most of the time the "row numbers" would sequential, but there would be exceptions... ugly.

  3. A nonce - "number used once" - using a hash algorithm would assign truly unique ID's, but it's not very useful in any other way (i.e. it is not ordered).

If you are also looking for database specific solutions, some of them will support "Triggers" or other custom code that you can use to manage the numbering for you (like the "autoincrement" option I mentioned above; but Oracle or MS SQL have other more complex functions/features).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.