Geek Answers Handbook

How to avoid database race condition when manually incrementing a new line PC

I have an obsolete data table in SQL Server 2005 that has a PC with no identifier / auto-increment and no way to implement it.

As a result, I have to manually create new entries in ASP.NET using the ole command "SELECT MAX (id) + 1 FROM" - before inserting it.

Obviously, this creates a race condition on the identifier in the case of simultaneous inserts.

What is the best way to gracefully resolve a racing event? I am looking for VB.NET or C # code ideas along the collision detection lines, and then trying again to fail the insert, getting another max (id) + 1. Can this be done?

Thoughts? Comments? Wisdom?

Thanks!

NOTE. What if I cannot modify the database in any way?

+5
source share
7 answers

The inability to change the database schema is tough.

If you insert an existing PK into the table, you will get a SqlException with a message about violation of PK restrictions. Catch this exception and try pasting several times until you succeed. If you find that the collision speed is too high, you can try max(id) + <small-random-int>instead max(id) + 1. Please note that with this approach, your identifiers will have spaces, and the space of spaces will be exhausted earlier.

id . , Interlocked.Increment , . , . Interlocked.CompareExchange:

class Autoincrement {
  static int id = -1;
  public static int NextId() {
    if (id == -1) {
      // not initialized - initialize
      int lastId = <select max(id) from db>
      Interlocked.CompareExchange(id, -1, lastId);
    }
    // get next id atomically
    return Interlocked.Increment(id);
  }
}

, , Autoincrement.NextId .

+4

. aux, . , aux, - .

+6

.

?

INSERT (PKcol, col2, col3, ...)
SELECT (SELECT MAX(id) + 1 FROM table WITH (HOLDLOCK, UPDLOCK)), @val2, @val3, ...

, , :

INSERT (PKcol, col2, col3, ...)
VALUES ((SELECT MAX(id) + 1 FROM table WITH (HOLDLOCK, UPDLOCK)), @val2, @val3, ...)

, .

  • INSERT.
  • HOLDLOCK, UPDLOCK MAX.

. , .

.

, ...

+3

. , , . .

SQL Express, .

-: SQL Express - [ids] [new_id]. [ids], [new_id] PK , .

-: , , / (.. Increment = 3 seed = 1/2/3 -).

+3

( id insert) ?

.

+2

. , , , , , PK. , , , .

+1

? , (, , ) ?

, .

, ... , . , , , , - . , (, , ) . , ( , SQL "TRANSACTION", ), .

, , , , , , (, "" , ), , " " , - . , , , , .. , , "" .

+1

More articles:


All Articles