TPQConnection transaction handling
Original Reporter info from Mantis: desmondc
-
Reporter name: Desmond C
Original Reporter info from Mantis: desmondc
- Reporter name: Desmond C
Description:
When setting .Action to caNone on TSQLTransaction, can we avoid running "begin" and "rollback" before and after query exec?
Postgres supports implicit transactions. An "idle in transaction" process on the pgsql server can be avoided. Especially useful doing DDL updates on production pgsql db's. A "idle in transaction" process causes locks which may block a DDL statement from running that alters the table(s) involved.
Although the immediate "rollback" releases any locks, it is a performance hit if used unnecessary, and, a block will be experienced when a table altering ddl statement is in progress that only adds columns to a table
Additional information:
Typically, a DB admin will have to kill all the "idle in transaction" process to get the DDL statement to run past the locks.
Mantis conversion info:
- Mantis ID: 17713
- Build: rc1
- Version: 2.4.0
- Fixed in version: 3.1.1
- Target version: 3.0.0