Drawbacks of Using Persistent PDO Connections: Unexpected Consequences

While persistent connections in PDO aim to enhance performance by caching and reusing connections, they can also introduce unintended consequences that can hinder performance.

Transaction and Connection State Issues:

A significant drawback of persistent connections is that unexpected script terminations leave open connections, which can lead to various problems:

• Locked Tables: If a dead script locked tables, they remain locked until the persistent connection releases them, blocking potential access from other scripts.
• Deadlocked Transactions: A terminated script's active transaction can block tables until a deadlock timer kicks in, potentially killing newer requests instead of the offending script.
• Inconsistent Transaction States: The next script that uses the persistent connection also inherits the transaction state. This can lead to inappropriate commits, rollbacks, or other unexpected behavior.

Connection Housekeeping Overhead:

To mitigate these issues, constant cleanup efforts are necessary for persistent connections. Every script must attempt to fix any dirty connections it encounters, which can add significant overhead depending on the database system.

Prefer Native Connection Pooling Mechanisms:

It's important to consider that modern databases like PostgreSQL have built-in connection pooling mechanisms that provide more efficient and stable pooling without the risks associated with plain vanilla PHP persistent connections. These native mechanisms are generally the preferred approach.

Anecdotal Evidence and Caveats:

As an example, the use of persistent connections led to unexpected problems at a workplace, including frequent connection issues, locked tables, and abandoned transactions. This highlights the potential consequences of using persistent connections without proper handling.