[odb-users] Isolation Level

[Boris Kolpackov]

Hi Chew,

Chew Kok Keng <ckokkeng at dso.org.sg> writes:

> May I know whether setting Isolation Level is supported in ODB for
> database servers such as PostgreSQL, MySQL, Oracle? If not, is there
> a workaround to be able to set isolation level?

For PostgreSQL you can use the SET TRANSACTION and SET SESSION
commands to specify the isolation level for the current transaction
and for all transaction on a connection, respectively. For example:

transaction t (db.begin ());
db.execute ("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

...

t.commit ();


And:

db.connection ()->execute ("SET SESSION CHARACTERISTICS AS "
                           "TRANSACTION ISOLATION LEVEL SERIALIZABLE");

Note that this last example will only work if you don't use the
database from multiple threads (more precisely, if you only use
one connection at a time). If your application can request multiple
connections, then the best way is to override the create() function
on connection_pool_factory and configure the isolation level there.
For example:

class serializable_connection_pool_factory: 
  public odb::pgsql::connection_pool_factory
{
public:
  virtual pooled_connection_ptr
  create ()
  {
    pooled_connection_ptr c (connection_pool_factory::create ());

    c->execute ("SET SESSION CHARACTERISTICS AS "
                "TRANSACTION ISOLATION LEVEL SERIALIZABLE");
    return c;
  }
};

See Section 17.3, "PostgreSQL Connection and Connection Factory"
for more information on connection factories.

MySQL also has the SET TRANSACTION command with slightly different
syntax and semantics, but the same basic logic applies.

Finally, Oracle is very similar to PostgreSQL and provides both
SET TRANSACTION and ALTER SESSION SET ISOLATION_LEVEL commands
with exactly the same semantics but just slightly different
syntax.

Let me also mention the other two databases, SQL Server and SQLite,
for completeness.

While SQL Server supports the SET TRANSACTION command, it is
recommended that we to do it via the ODBC API call so that the ODBC
machinery knows about the change. As a result, the odb::mssql::database
class constructor has an argument that allows you to specify the
isolation level.

SQLite has a number of pragmas that work similar to the SET SESSION
command, for example:

PRAGMA read_uncommitted = 1;

Boris