Multiple Active Statements (MAS) and DBD::ODBC

Although this is a generic document about using Multiple Active Statements (MAS) with DBD::ODBC, the principal focus is on using MAS with Microsoft SQL Server, as ODBC drivers for most other databases support MAS out of the box.

Although most of the examples in this document are written in Perl, the majority of the information on MAS, which the examples are used to illustrate, is programming language neutral.

If you require further information about DBD::ODBC, refer to the tutorials listed in the Resources appendix.

What are Multiple Active Statements (MAS)?

Multiple Active Statements (MAS) are more than one statement in the same connection that have active work (or result-sets, Multiple Active Result Sets (MARS)). A typical example:

# connect and create a test tablemy$h= DBI->connect();$h->{RaiseError} =1;
eval {local$h->{PrintError} =0;$h->do(q/drop table example1/)};$h->do(q/create table example1 (a int not null primary key, b char(10))/);foreach(1..2) {$h->do(q/insert into example1 values(?,?)/, undef,$_,'fred');}# loop through all the rows in the table changing each one's b field to# 'dave' yes, we know no one would really do this - it is just an examplemy$s1=$h->prepare(q/select a from example1/);$s1->execute;my$s2=$h->prepare(q/update example1 set b = ? where a = ?/);while(my$row=$s1->fetchrow_arrayref) {$s2->execute('dave',$row->[0]);}

When you run this with some ODBC drivers, you typically get an error on the $s2->execute saying something like "connection is busy with results of another hstmt".

How to overcome MAS restrictions in an ODBC driver neutral way

When people first started hitting MAS restrictions, the obvious solution was to create multiple connections, each running one active statement. Using this solution, our above example becomes:

my$h1= DBI->connect();my$h2= DBI->connect();$h1->{RaiseError} =1;$h2->{RaiseError} =1;
eval {local$h1->{PrintError} =0;$h1->do(q/drop table example1/)};$h1->do(q/create table example1 (a int not null primary key, b char(10))/);foreach(1..2) {$h1->do(q/insert into example1 values(?,?)/, undef,$_,'fred');}# loop through all the rows in the table changing each one's b field to# 'dave' yes, we know no one would really do this - it is just an examplemy$s1=$h1->prepare(q/select a from example1/);$s1->execute;my$s2=$h2->prepare(q/update example1 set b = ? where a = ?/);while(my$row=$s1->fetchrow_arrayref) {$s2->execute('dave',$row->[0]);}

You often see code that opens a connection, issues the update and closes the connection inside a loop (which is even worse). However, this is generic solution for an ODBC driver that does not support MAS, and, although this solution works:

It requires multiple connections and hence more resources.

Contributors to the code needs to know it uses MAS and open additional connections for each MAS.

Licensing in database engines is often based around the number of concurrent connections.

It can cause deadlocks, depending on the isolation level, as it uses two different connections and hence introduces two different and potentially conflicting environments.

A note about default result-sets in Microsoft SQL Server

Results are generally produced by SELECT and FETCH statements (although procedures and functions with output parameters and return values also do this). Microsoft SQL Server executes a SELECT statement by streaming the results back to the client and, as rows are produced by the query, they are written back to the caller over the network. If the client fails to read the network data as quickly as Microsoft SQL Server writes it, a point is reached where SQL Server will block, and execution is suspended until the client reads more data. Note that in this scenario, the client does not need to ask for more data from the result-set. The result-set is continually streamed until there is no more to send and if at any point the client knows it needs no more (if the cursor is closed), it has no choice but to read the remaining result-set. (This is also the reason why issuing a query that returns a huge result-set, of which the client reads only a few rows before closing the cursor, can take a long time to complete, even though the application has not seen all the result-set.) This producer/consumer scenario was previously referred to as "firehose cursors" and is now more generally known as "default result-sets".

Driver specific ways around MAS restrictions

At the time of writing, there are three main ways to get around MAS restrictions without resorting to multiple connections. The following script provided by the DBIx::Class team tests each workaround out and whether the workaround is required:

Microsoft SQL Server ODBC Driver

Changing the SQL_ROWSET_SIZE value

A long time ago in the history of DBD::ODBC, someone discovered the first "hack" to get around MAS restrictions. The hack was based around the discovery that if you set SQL_ROWSET_SIZE to anything greater than 1, Microsoft SQL Server would allow MAS. The odbc_SQL_ROWSET_SIZE connection attribute was added to DBD::ODBC, and so our basic example now becomes:

# connect and create a test tablemy$h= DBI->connect();$h->{RaiseError} =1;
eval {local$h->{PrintError} =0;$h->do(q/drop table example1/)};$h->do(q/create table example1 (a int not null primary key, b char(10))/);foreach(1..2) {$h->do(q/insert into example1 values(?,?)/, undef,$_,'fred');}$h->{odbc_SQL_ROWSET_SIZE} =2;# loop through all the rows in the table changing each one's b field to# 'dave' yes, we know no one would really do this - it is just an examplemy$s1=$h->prepare(q/select a from example1/);$s1->execute;my$s2=$h->prepare(q/update example1 set b = ? where a = ?/);while(my$row=$s1->fetchrow_arrayref) {$s2->execute('dave',$row->[0]);}

This works because setting the SQL_ROWSET_SIZE attribute forces Microsoft SQL Server to use a server forward-only cursor. This does not harm DBD::ODBC, as DBD::ODBC never uses SQLExtendedFetch, which SQL_ROWSET_SIZE affects. However, this workaround has side effects that result in more round trips to the server when retrieving data and introduce potential problems if you do not ensure you retrieve all result-sets before closing a statement (see the DBD::ODBC POD).

There is an additional problem if you attempt to set odbc_SQL_ROWSET_SIZE in the connect call (on some platforms with some ODBC driver managers) with versions of DBI before 1.616 or DBD::ODBC before 1.28_1. A bug in DBI before 1.616 causes it to FETCH all attribute values passed on the connect method call, and some driver managers will error if asked for SQL_ROWSET_SIZE on a connection handle when running in an ODBC 3.0 connection. You can overcome this problem by:

Installing DBI 1.616 or above.

–Or–

Installing DBD::ODBC 1.28_1 or above (which caches the value).

–Or–

Setting odbc_SQL_ROWSET_SIZE on the connection handle after the connect call.

Directly changing the cursor type

Similar to the SQL_ROWSET_SIZE workaround, it is possible to achieve MAS support by directly change the cursor type. If you use a dynamic cursor, you will get MAS support. Our simple example now becomes:

# connect and create a test tablemy($dsn,$user,$password) = ('dbi:ODBC:asus2','sa', undef);my$h= DBI->connect($dsn,$user,$password, {odbc_cursortype =>2});# NOTE if you are using DBI after 1.41 you can use# odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC$h->{RaiseError} =1;
eval {local$h->{PrintError} =0;$h->do(q/drop table example1/)};$h->do(q/create table example1 (a int not null primary key, b char(10))/);foreach(1..2) {$h->do(q/insert into example1 values(?,?)/, undef,$_,'fred');}# loop through all the rows in the table changing each one's b field to# 'dave' yes, we know no one would really do this - it is just an examplemy$s1=$h->prepare(q/select a from example1/);$s1->execute;my$s2=$h->prepare(q/update example1 set b = ? where a = ?/);while(my$row=$s1->fetchrow_arrayref) {$s2->execute('dave',$row->[0]);}

The first thing to note here is that you need to set the cursor type in the connect call, as setting it later on the connection is not supported by DBD::ODBC.

As with setting odbc_SQL_ROWSET_SIZE, the main disadvantage of setting a server cursor is that more round trips between the client and server are required, since the client has to keep asking for more rows.

Using MARS_Connection

Since SQL Server 2005, you can use the MARS_Connection connection attribute to allow Multiple Active Result Sets (MARS). In ODBC, you do this by adding "MARS_Connection=yes" to the connection string. Our example now becomes:

# connect and create a test tablemy($dsn,$user,$password) =('dbi:ODBC:DSN=nativec;MARS_Connection=Yes','sa', undef);my$h= DBI->connect($dsn,$user,$password);$h->{RaiseError} =1;
eval {local$h->{PrintError} =0;$h->do(q/drop table example1/)};$h->do(q/create table example1 (a int not null primary key, b char(10))/);foreach(1..2) {$h->do(q/insert into example1 values(?,?)/, undef,$_,'fred');}# loop through all the rows in the table changing each one's b field to# 'dave' yes, we know no one would really do this - it is just an examplemy$s1=$h->prepare(q/select a from example1/);$s1->execute;my$s2=$h->prepare(q/update example1 set b = ? where a = ?/);while(my$row=$s1->fetchrow_arrayref) {$s2->execute('dave',$row->[0]);}

Note that you need SQL Server 2005 or later to use MARS_Connection. Also, if you are using the Microsoft SQL Server ODBC Driver instead of the Native Client Driver, you need the ODBC driver from the SQL Server 2008 distribution.

Note that although this is a connection attribute, there is no way to enable MARS_Connection by using the Microsoft ODBC Driver GUI setup. You also need to enable this attribute at connection time or before the connection by setting SQL_COPT_SS_MARS_ENABLED (via SQLSetConnectAttr, which you cannot directly do with DBD::ODBC).

Notes:

Do not just enable MARS for your application without reviewing the code, as it may show reduced performance, because the default method used by Microsoft SQL Server is pretty network efficient.

MARS provides interleaving of result-sets, but things can go horribly wrong if, for instance, you change server state with 'set' or 'use' or use T-SQL transaction statements.

Do not consider using MARS if you require locking on rows in the result-set.

You cannot begin a transaction when a result-set is already open and if you commit or rollback a transaction, any open result-sets become unusable.

If you need "Preserve On Commit" behavior, you must use a server cursor.

Using SQL_COPT_SS_PRESERVE_CURSORS

You can use the connection attribute SQL_COPT_SS_PRESERVE_CURSORS, when set before opening a connection, to enable MAS. The following C code illustrates this on a table defined as:

create table xtest(ky int primary key, txt varchar(100));

Insert rows with ky values of 1 to 100 and any txt values. The code reads one row at a time, and, for each row where kymod 4 is 0, it updates the row (in another statement) to set the ky value to "WORLD". You cannot currently do this in DBD::ODBC.

Article Feedback

* Did this content help you?

YesNoMaybe

* Please select one option based on your first choice:

I'm very satisfiedI think it will help, but I haven't tried it yetIt is helpful, but I need more informationIt is helpful, but hard to understandSeemed relevant in search results, but didn't help meThe information is incorrectThe page contains one or more broken links