Re: sqlite3 dbd provider question

[Tom Donovan]

William A. Rowe, Jr. wrote:
William A. Rowe, Jr. wrote:
I'm reviewing the providers themselves and trying to determine how and 
when the dbd lock api should be used vs. a dso-module apr-util wide mutex 
should be used instead.l

The oracle provider alone is using this lock to prepare statements.  
While the sqlite3 provider seems to use it much more extensively, and the other
providers aren't using it at all.

SQLite is not entirely thread-safe, although the current release claims to be 
for read-only access.

Oracle, MySQL, PostgreSQL, and most other databases are (or at least claim to be) thread safe, and a 
connection can be reused by different threads.

In the case of the Oracle dbd driver, it uses the lock to maintain its own cache of statements if 
GLOBAL_PREPARED_STATEMENTS is defined - not because any Oracle OCI functions require it.

Can someone explain the discrepancy, and if they shouldn't be 
per-provider mutexes so we aren't blocking in silly ways between providers accessed by
different threads?

It appears to be a presumption that only one dbd provider is used in an application. A private 
per-provider lock seems like a much better idea for dbd drivers which use it like SQLite & Oracle do.

And a related question, any prepared statement can be portably used across
any of the databases open within the specific provider.  Correct?

In general no. Most databases consider a prepared statement to be associated with a single database. 
This would seldom make sense, but if both databases happen to have the same table names and field 
names, a SQL statement could be valid for two databases - so a database vendor could choose to allow 
this.  I think Oracle might allow this - but I have never tried it.

-tom-