Database Reference
In-Depth Information
The SQL Client calls the
sp_reset_connection
system stored procedure when it reuses a connection from the
pool. That procedure re-authenticates on SQL Server, making sure that the login credentials are still valid, and resets
the connection context. There is one very important catch, however. The
sp_reset_connection
procedure does not
reset the transaction isolation level used by the previous session. This behavior can introduce very unpleasant and
hard to reproduce issues in client applications.
■
Note
We will discuss transaction isolation levels and locking behavior in greater detail in Chapter 17, “Lock types.”
Consider the situation where an application needs to generate a unique value for the primary key column. One
of the common patterns in this case is a
counters
table that stores the name of the counter and the most recently
generated key value. When a new unique key needs to be generated, the application can select and increment a
specific counter value from the table using the
SERIALIZABLE
transaction isolation level to prevent other sessions from
accessing it.
Unless the application resets the isolation level before closing the connection, the session that reuses the
connection from the pool would run in
SERIALIZABLE
isolation level. This greatly increases blocking in the system, and
it is very confusing and hard to troubleshoot. Even though you can detect blocking and see that the
SERIALIZEABLE
isolation level is in use, it is very hard to explain why it happens unless you are aware of such behavior.
■
Consider setting the desired transaction isolation level in each session after you open a connection to avoid
this issue.
Tip
■
Note
You can read more about connection pooling at:
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
.
Working with Database Tables Directly
Code that works with database tables directly is very common, especially in the small applications. This approach
can be convenient at beginning of the development process when everything is fluid and the code is frequently
refactored. Developers can change queries on the fly without any dependencies on either the database changes or
other members of the team.
Unfortunately, this situation changes when the system is deployed to production. The queries are compiled
into the application. If you detect errors or inefficiencies that cannot be addressed at the database level, you
will need to recompile, retest, and redeploy the application to fix them. This could be very time consuming and
resource intense.
While it is not always the case, direct access to database tables often negatively affects the separation of
duties within the team. Every developer writes T-SQL code, which is not always an optimal solution. It also
increases the chance of difficult-to-support spaghetti-like code when the data is accessed from different layers
within the system.
