Introducing Multiple Active Result Sets (MARS) (2)

[SQL-Server-Team]

First published on MSDN on Jun 23, 2006

MARS is a powerful tool, but you may shoot yourself by the foot if you don't use it correctly. In the following several talks, I will talk about how to use MARS safely. This talk will cover the session/request context management. In later talks, I will cover transaction usage, security context usage and yield/resume logic, etc.

As I said in the first post, if you send multiple requests to server in one session, these requests share the same session level context, such as current session database, security setting, transaction, set options (transaction isolation level, for example) and other resources such as cursors, handles, local temporary tables etc. But how exactly SQL Server implements the sharing?

Server uses different mechanisms for different kinds of session context or resource to be shared.

1. Clone/Merge. Session keeps one master copy of the context, and each running request keeps its own copy of the context. The synchronization of the session and request copy is done at beginning and end of a request by doing clone and merge. Most session contexts are using this mechanism as explained below.
   
2. Protected access to session. All requests running in a session share the same copy of information, and any access to the shared information are protected in some way. Transaction context, security context, and other session level resources like cursor, prepared handle etc are falling into this category. The detail of this will be covered in next post.

This post will talk in detail about the clone/merge mechanism.

Session itself maintains all the contexts session has -- set options, current database, some session global values such as @@rowcount, @@error, etc. We call it the session environment. The environment values are changed as a result of executing some user request.

At the beginning execution of each request, the request makes a copy of session environment (we call clone). The request can make any change to these environment variables during execution, but these changes are kept local to the request, neither session nor other running request can see these changes. After the request is finished, all the environment values are copied back to the session (we call merge). If there are new requests started before this request copies its value back to session, the new request will get an old copy of the session context.

Most session context falls into this clone/merge category. They include:

1. All the set options

2. Session current database

3. Session global variable -- @@error, @@rowcount, @@identity, etc

Example1: database context:

Suppose a session is current running in database DB1. One request starts and copies the database context from session, which is DB1. Now the request runs the following:

UPDATE t1 SET col1 = 2       -- this will change table t1 in DB1

USE DB2

UPDATE t2 SET col1 = 3       -- this will change table t2 in DB2

While this request is running, the session’s current database stays as DB1 until the request merges its change back to session. If client submits another request

UPDATE t1 SET col = 4

to server while the first request is running, and the new request reaches server before the first request finishes, then the new request will run in DB1, but if the new request reaches server after the first request is finished, then it will run in DB2. As you may already figured out, this is very risky – we may change data in a database we don’t intent to, and cause database corruption!

Example2: SET Options

If one request changes XACT_ABORT setting in the middle of its execution, then another request that is submitted to server after this request may or may not get to run under this new setting depend on different aspects, and the error handling policy will be different for the new request. This may cause surprise for you – in your testing environment you get one behavior, and in your production environment you get another behavior, simply because your database grows bigger.

Example3: @@ values

The @@values are copied back from request to session at the end of the request. When you check @@ values inside a request immediately after a statement, like this:

-- the two statements are in one request(batch)

UPDATE t1 SET col1 = 1

SELECT @@rowcount

you are getting the value from the request’s local environment copy, so it can reflect the value you expect. But if you submit the two statements from two batches:

-- first request sent to server

UPDATE t1 SET col1 =1

-- second request sent to server after the first request

SELECT @@rowcount

Then whether or not the @@rowcount can reflect the UPDATE result depends on:

1. Whether or not the SELECT starts to run AFTER first request is finished, and
   
2. Whether or not there are other requests running in the same session in the mean time.

The bottom line

So what do you need to keep in mind because of this clone/merge semantics?

You should always change session level settings including set options and database context with multiple result sets open. As the examples above shown, it is very hard for client to control when exactly the change you made in one request will affect other requests, so server may end up processing a request under the wrong settings.

As you will understand in my next post, when exactly the changes you made in one request will be reflected in session is affected by different factors -- your result set size, network packet size, how client code consumes the result set, etc. So unless you know very well what you are doing, do not make session scope change with multiple requests running.