sql - using a transaction to avoid a race -

I am writing a daemon to monitor the creation of new items, which detects new things when a database We will call object widgets that adds rows to the table. The code flow is approx:

  1: find each time: 2: Find the latest N widget (from external source) 3: foreach widget 4: if (widget is not in database right now) 5: Widget Add rows for  

The last two lines present a race condition, because if two instances of this daemon are running at the same time, then make a line for the widget X If the time line up can

The most obvious solution is to use a unique constraint on the widget identifier column, but it is not possible due to the database layout (this is actually allowed for one widget from one There are more lines, but Damon should never do it automatically).

My next idea is to use the transaction, because this is what is their purpose ADO In the net world, I believe I want a different level, but I am not positive. Can anyone tell me in the right direction?

Update : I did some experimentation, and the serialized transaction does not appear to solve this problem, or at least not very well. The interesting case is described below, and it assumes that only one table is included. Note that I'm not positive about lock details, but I think I am right:

  Thread A: performs line 4, get reading lock on table Thread B : Perform line 4, get reading locks on the table Thread A: tries to execute line 5, for which it is necessary to upgrade to a write lock (it is necessary to wait until the list of Thread B is unlocked. Thread B: Line 5 tries to execute, again a lock upgrade is required (Thread A requires waiting for it to be unlocked)  

This gives us a Classic deadlock leaves in position. Other code paths are possible, but if Threads A and B do not interleave, then there is no synchronization problem anyway. The final result is that SQL detected the deadlock and after finishing one of the statements, one SQL Extension was thrown at one thread. I can catch this exception and find out the special error code, but he does not feel very clean.

I can create another path that is to create a second table that tracks widgets viewed by the daemon, where I can use a unique constraint Do it still need to catch and detect some error codes (in this case, the violation of integrity constraints), so I'm still interested in a better solution, if one can think of one.

Isolation level "serial" should really work. It does not allow the data read from one transaction to be replaced by another. But it does lock a lot and should not be used normally, because it slows down the application and there is a high risk of dead lock.

Options:

• You only lock the entire table to ensure that no one does not write in, when you are checking There are some things that are there (no) the problem is that only one can write in the table at a time, which means that it makes all things very slow (you can search for data, if so, then < Em> not , lock the table before inserting it and search again . This is a normal petter Do not.)
• To be honest, you should think about the fact that you try to insert two transactions at the same time on the same line. It's likely you should start solving it. Only one daemon should be responsible for the same data.
  • Every daemon handles his data.
  • Every daemon calls service instead of database. There you can put things together before inserting it.

Anyway, you need a unique identifier to clearly identify the data. If you do not have a unique identifier, how can you find it in the database?