开发者

Data Concurrency issue in Windows Service

开发者 https://www.devze.com 2023-03-05 17:38 出处：网络

I am having an issue with inserting data in the SQL 2008 table. I have 6 SPs and they all run in the transaction from ADO.Net code. First 3 SPs delete the data and next 3 SPs insert the data in those

相关专题：concurrency sql-server-2008 stored-procedures windows-services

I am having an issue with inserting data in the SQL 2008 table.

I have 6 SPs and they all run in the transaction from ADO.Net code. First 3 SPs delete the data and next 3 SPs insert the data in those table. These SPs are part of a windows servic开发者_JAVA百科e. I noticed that when a single instance of service is running, everything works fine but if multiple instances of service are running, i am running into Data Concurrancy issue. One of the SP throws an error with PrimaryKey violation from one service while the same SP with the same data running from the different server works fine.

I have set my Transaction Isolation level to RepeatableRead in ADO.Net code.

Can someone tell me what am i missing here? Should i use HOLDLOCK in the SP while inserting the data?

You need to up the isolation level to Serializable to make sure the other instance can't update the data while the transaction is in place. See here for more info.