I am porting a RMM application(.Net) from Windows to MAC OS X. The currect code was written for SQL Server , and has many cases where we use nested Transaction scopes and is multi threaded. In one case I get a "Error 455 (Provider v. 126.96.36.1999): Concurrency error. The row is being updated or deleted by another active transaction" perhpas once every 6-12 hours. Not often enough to debug but too often to ignore. I finally manage to craft some sample code to make a hard fault.
Start with 2 threads.
Each Thread creates a TransactionScope (ts), registers a TransactionComplete handler, creates a VistDBConnection adn Begins a Transaction, does a select and update, then a ts.Complete which fires adn event that committs the transaction and disposes the connection. Then add a Barrier to force both threads to sync within the TransactionScopes. error every time.
Note we have two TransactionScopes, one on each thread, 2 connections, 2 transctions, pooling false, NonexcluseiveReadWrite, transactionMode.On
Why the doesn't the thread that gets the locks first block the second thread instead of aborts/exceptions
We've been working with Tom through a Ticket, but for anyone else who runs into this issue... As an immediate workaround, we suggest you try treating it like a deadlock situation and automatically retry the failed transaction(s).
We're also looking into what we might be able to tweak in the engine in the short-term to make this behave better and/or fail less often. And in the long-term we were already planning a thorough review and redesign of how VistaDB handles locking and transactions in order to improve multi-user concurrency.