0

We have an application running on clinet which is used by about 30 concurrent users. In the application there is a Sub which deletes and inserts records in the underlying SQL-Server-Database.

The ID is a primary key in table A. Table B is filled in dosomething(ByVal ID as Integer) based on various data coming from Table A. Entries in Table B have a reference to table A (B.ID_A).

Due to the fact that this sub needs to be called from various places in the code and the number of concurrent users running the app at the same time we had some troubles with race conditions in the past. This resulted in data duplication because the code was running simultaneously.

Public Sub dosomething(ByVal ID As Integer)


End Sub

My question is if there is any way to prevent concurrent execution of this sub with the same ID at the same time in a multi-user application?

I've read about SyncLock but this doesn't solve the problem when running multiple instances of the application, right? Another solution i could think of is creating a Database Table where IDs are temporarily inserted as primary key at the beginning of the sub. This would result in the later execution getting an SQL-Exception when trying to insert a duplicate primary key i guess.

bautista
  • 765
  • 10
  • 26
  • It sounds like you shouldn't be inserting IDs at all. Where do these IDs come from? Why can the database not generate the ID? – jmcilhinney Dec 12 '22 at 09:45
  • @jmcilhinney These IDs are generated in the Database and are a foreign key for the records which are about to be deleted/inserted in another table. I have edited the question accordingly. – bautista Dec 12 '22 at 10:26
  • You could consider using one of the concurrent collections e.g. a `ConcurrentDictionary` to keep track of operations in flight. I'd suggest taking another look at your logic, though... if the IDs are being generated in the database, then presumably only one user ought to be working with one ID (and only one set of operations should be running on that ID). – Craig Dec 12 '22 at 15:03
  • 2
    Have you considered using [transactions](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver16), also available as an [ADO.NET object](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/local-transactions) – Hel O'Ween Dec 12 '22 at 15:13

0 Answers0