16

I am looking for a way to specify a custom transaction level in Delphi's DBX using the firebird driver. I am using Delphi XE.

In the Parameter editor of the TSQLConnection component I can set various TransIsolation values and I'm using the default of ReadCommited. The property WaitOnLocks is set to True. I expect this uses the following firebird transaction configuration but I'm not sure:

READ WRITE + WAIT + SNAPSHOT (see http://www.firebirdsql.org/refdocs/langrefupd20-set-trans.html)

I would like to use LOCK TIMEOUT [seconds] instead of WAIT. But I can't find how or where to specify this. I've looked in the DBX source files and there is some code for custom transaction levels (search for xilCUSTOM) but it seems unfinished/unused.

The reason for this is that we sometimes experience a deadlock in our multi-user datasnap application and I think it's because one transaction is waiting on another (possibly dead) transaction. We would rather have the transaction break after a couple of seconds than have the whole client hangup indefinitely.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Ronald
  • 171
  • 1
  • 4
  • 1
    Following documentation `In addition, TSQLConnection lets you specify database-specific custom isolation levels. Custom isolation levels are defined by the dbExpress driver. See your driver documentation for details.` – JustMe Mar 01 '12 at 08:37
  • Is it in your consideration to buy a documented interbase driver? Or use more native salution like IBDAC or IB Objects (+performance + compatibility +support)? – JustMe Mar 02 '12 at 09:17
  • Apart from you issues with the wait timeout, absolutely make sure you understand the deadlock conditions occurring in your application. This smells of a flawed Transaction logic, very long running transactions, unsafe use of triggers etc. which will only get worse over time, Timeout or not. – Daniel Apr 11 '20 at 14:55

0 Answers0