0

We are using ASP.net MVC 4 on DB/2 database. How can I create a way to avoid data concurrency and give a user a unique number to be stored in a unique field of a table and handled on the database side?

If I create a stored procedure to do this, how can I return a static number to make sure each call will return an incremented number and not just check a specific column's max value and adding one.

eaglei22
  • 2,589
  • 1
  • 38
  • 53
  • By using a database transaction... – Henk van Boeijen Feb 10 '16 at 20:01
  • By using a `SEQUENCE` – mustaccio Feb 10 '16 at 20:20
  • ... do you actually need a completely sequential number, with no gaps? This is actually rare - most of the time, you just want a **unique** number (which is what `SEQUENCE` or an auto-generated column gets you). `MAX(column) + 1` is actually a _really terrible_ idea, and shouldn't be used. – Clockwork-Muse Feb 12 '16 at 05:56
  • @HenkvanBoeijen - A db transaction won't really help here. Unless you meant for the `MAX(column) + 1` scenario, but that would, at minimum, require locking the entire table for update (and potentially for read, depending on how that statement is being used), which would be a major problem in any concurrent system. – Clockwork-Muse Feb 12 '16 at 06:02
  • @Clockwork-Muse - Simply add a table holding the most recent issued number, read and increment it in a db transaction (preferrably using a stored procedure). When needed, apply row locking. – Henk van Boeijen Feb 12 '16 at 06:06
  • @HenkvanBoeijen - Uh, yeah, that's **if** they need a completely sequential number (no gaps), which is rare. Use it for things like invoice numbers or similar, where there's usually legal requirements. And the locking is required on the "key" table. For a surrogate key, you want something fast, with (probably) no lock. – Clockwork-Muse Feb 12 '16 at 06:17
  • Possible duplicate of [How to generate the primary key in DB2 (SQL Optimization)](http://stackoverflow.com/questions/9703554/how-to-generate-the-primary-key-in-db2-sql-optimization) – Clockwork-Muse Feb 12 '16 at 06:17

0 Answers0