Opened 7 years ago

Closed 7 years ago

#325 closed defect (wontfix)

List of Queries not thread-safe

Reported by: Maxim Reznik Owned by:
Priority: major Milestone: 0.6.0
Component: Matreshka - SQL Version:
Keywords: thread-safe database query Cc:

Description

Database has list of its queries. (See Matreshka.Internals.SQL_Drivers.Abstract_Database)

This list is not thread-safe. This cause memory problems if two task create/free queries in parallel.

Change History (3)

comment:1 by vadim.godunko, 7 years ago

Use of SQ_Database and SQL_Query object across threads is not defined now. It seems it is time to do this.

Each database has own thread safety rules. They need to be analyzed to be able to make decision.

MySQL

"The client library is thread-safe per connection. You can let two threads share the same connection with the following caveats:

Multiple threads cannot send a query to the MySQL server at the same time on the same connection. In particular, you must ensure that between calls to mysql_query() and mysql_store_result() in one thread, no other thread uses the same connection. You must have a mutex lock around your pair of mysql_query() and mysql_store_result() calls. After mysql_store_result() returns, the lock can be released and other threads may query the same connection.

If you use POSIX threads, you can use pthread_mutex_lock() and pthread_mutex_unlock() to establish and release a mutex lock.

Many threads can access different result sets that are retrieved with mysql_store_result().

To use mysql_use_result(), you must ensure that no other thread is using the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to use mysql_store_result()."

Multithread use of MySQL prepared statement C API is not defined in documentation.

PostgreSQL

"One thread restriction is that no two threads attempt to manipulate the same PGconn object at the same time. In particular, you cannot issue concurrent commands from different threads through the same connection object. (If you need to run concurrent commands, use multiple connections.)"

SQLite3

"SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the sqlite3_threadsafe() interface to find out.

Prior to version 3.3.1, an sqlite3 structure could only be used in the same thread that called sqlite3_open() to create it. You could not open a database in one thread then pass the handle off to another thread for it to use. This was due to limitations (bugs?) in many common threading implementations such as on RedHat9. Specifically, an fcntl() lock created by one thread cannot be removed or modified by a different thread on the troublesome systems. And since SQLite uses fcntl() locks heavily for concurrency control, serious problems arose if you start moving database connections across threads.

The restriction on moving database connections across threads was relaxed somewhat in version 3.3.1. With that and subsequent versions, it is safe to move a connection handle across threads as long as the connection is not holding any fcntl() locks. You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized.

Under Unix, you should not carry an open SQLite database across a fork() system call into the child process. Problems will result if you do."

Oracle

The thread safety feature of Oracle Database and the OCI libraries allows developers to use OCI in a multithreaded environment. Thread safety ensures that code can be reentrant, with multiple threads making OCI calls without side effects.

Polling Mode Operations and Thread Safety

OCI supports polling mode operations. When OCI is operating in threaded mode, OCI calls that poll for completion acquire mutexes when the OCI call is actively executing. However, when OCI returns control to the application, OCI releases any acquired mutexes. The caller should ensure that no other OCI call is made on the connection until the polling mode OCI operation in progress completes.

Polling Mode Operations are not used in Matreshka OCI driver (yet?). These operations include piecewise operations and nonblocking operations. Nonblocking ops are not recommended:

Because nonblocking mode requires the caller to repeat the same call until it completes, it increases CPU usage. Instead, use multithreaded mode.

Sharing connection between threads possible, but has no much sense:

Most processing of an OCI call happens on the server, so if two threads using OCI calls go to the same connection, then one of them can be blocked while the other finishes processing at the server.

Firebird

Currently, the highest level of concurrency supported by any version of the Firebird client library is thread-safety at the connection level.

When we say that the Firebird client library is thread-safe at the connection level, we mean that it is safe to use a particular connection in only one thread at a time, although the same connection can be manipulated by different threads in a serial fashion, and different connections can be manipulated by different threads in parallel.

For example, in a multithreaded application server, it is safe for a particular connection to be leased from a connection pool by Thread A, used, and returned to the pool for later lease by Thread B. It is not safe for Thread A and Thread B to use the same connection at the same time.

Last edited 7 years ago by Maxim Reznik (previous) (diff)

comment:2 by vadim.godunko, 7 years ago

So, the best we can do here is to add check whether objects are in use by another thread and to raise Program_Error in this case.

comment:3 by vadim.godunko, 7 years ago

Resolution: wontfix
Status: newclosed

It is not clear how to implement checks efficiently. Closing ticket.

Note: See TracTickets for help on using tickets.