CODEWITHSUNDEEP
c#sql-serverpetapoco
hazard
hazard

Reputation: 418

PetaPoco transaction isolation level

I have a c# windows service which updates data in my database using PetaPoco, all wrapped into a transaction. However, I need the database to be available for queries while the transaction runs (web app, running queries through an ODBC connection), but if queried while the transaction is being committed, I get a "Catastrophic failure" error from my database. Currently testing against MS SQL Server, but also need this working against Oracle databases.

Is there any way to set the transaction isolation level for PetaPoco transactions, and could this be the solution in this case?

Upvotes: 0

Views: 1158

Answers (1)

hazard
hazard

Reputation: 418

Turns out that the "Catastrophic failure" messages originated from deadlocks in the database caused by updates from both PetaPoco and my existing web app. I resolved this by locking the tables with 

SELECT TOP 1 * FROM [TABLENAME] WITH (TABLOCKX)

queries at the start of the PetaPoco transaction.

Upvotes: 0

Related Questions