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.
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
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
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
Then whether or not the @@rowcount can reflect the UPDATE result depends on:
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.