That's a great question to answer on the Forums!

VistaDB gives a concurrency error when trying to modify a row that has been modified by a different transaction that has neither committed nor rolled back and whose state thus can not be determined.

This can happen under normal operation when separate connections have overlapping transactions (actual concurrency happening), and it should be handled by some sort of abort or delayed-retry mechanism depending on what is being attempted and on the structure of the application (for example, user-initiated operations can give feedback to the user and get their input on whether to retry (if appropriate) or to cancel--after which they might try the operation again from scratch with refreshed data--but when there is no user some sort of automatic retry--from an appropriate point and after some delay to allow the original transaction to resolve--might be appropriate).

When it remains a persistent error after all connections have been closed it is usually because the connection of the pending transaction was lost without going through normal disposal--such as stopping the debugger, killing the process, or a machine crash--since closing or disposing a connection would normally rollback any pending transaction.  It might also be possible if a connection instance (with a pending transaction) is garbage collected without having been closed or disposed because the GC dispose may not be able to clean up referenced objects such as the transaction properly.  (Thus, it's best practice to make sure connection objects get properly disposed when no longer needed by the use of a using statement or a finally block.)

This can leave a transaction marked in the database as still active when there is no longer a connection with ownership to ever commit it or roll it back.  Unfortunately, because VistaDB operates in a distributed manner with no central server there is no way for one connection to know that a connection in another process has definitively exited, so the "orphaned" transaction can't be automatically rolled back by other connections.  The result is a persistent "concurrency" error when trying to modify affected rows.

The solution for a persistent concurrency error is to perform a PackDatabase operation (a RepairDatabase is a form of PackDatabase, so it would also work).  Because this requires an exclusive file handle to the database, the pack operation knows that all other connections have closed, and any transactions not marked as committed can therefore never get committed and can be treated as rolled back.

I'm not sure what caused your access problem with the attempted RepairDatabase, but there seems to be an open connection that you didn't realize--perhaps even an active connection with the active transaction that hasn't completed yet!  If no copies of the application are still running with active connections, some other possible culprits can be a pooled connection in a still-running process or an open data connection in Visual Studio (if you used VistaDB's VS Designer extension tools).

Note that because the transaction state is marked in the file itself the copies will continue to exhibit the same concurrency error on the affected row(s) until a pack database is performed to reset the state of the file contents.  But, if you are able to copy the file then the new copy would not be open and a PackDatabase should at least be possible on that copy.  It may also mean that the connection on the original file has been closed (if the copying used an exclusive connection), so you might just want to try again to pack the original database file.

    -Rob