Dan Guzman's Blog

Concurrency is confusing.  Terms line “optimistic concurrency control” and “pessimistic concurrency control” can mean quite different things depending on the context.  I hope to clarify some points surrounding concurrency as it relates to application and database design with a series of posts.  In this first post, I’ll review some basic concurrency concepts and highlight some differences in concurrency models from a database and application perspective.

In computer science, “concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible” (from the Concurrency control wiki).  Every major DBMS supports ACID transactional reliability so one might think “correct results” would be a non-issue.  In practice however, both the application and database must work in concert to provide the appropriate level of data integrity and performance while minimizing user rework to address conflicts and deadlocks.

Concurrency Control:  Optimistic, Pessimistic and Chaos

“Optimistic”, “pessimistic” and “chaos” refer to the safeguards one takes based on the likelihood of concurrent updates and how much rework is acceptable.  Optimistic concurrency control is used when it is unlikely that different users will update the same data.  In the unlikely event that the same data is updated by different users, the conflict is detected when data are saved and the second user must redo/merge changes in order to prevent overwriting the changes made by the first user.  Users of a well designed optimistic concurrency application experience fast response time and are inconvenienced only in the rare case of an update conflict. 

Pessimistic concurrency control is used when it is likely that the same data will be updated by different users.  To prevent the need to redo or merge changes, an application serializes data access so that only one user can edit data at a time.  The obvious downside is that subsequent users must wait until preceding user(s) has completed their changes and this can increase response time or data unavailability.  However, overall user productivity can be better than optimistic currency control because rework is avoided. 

Chaos concurrency control (also known as Anarchy) is used in situations when concurrent updates are not possible or “last in wins” is acceptable.  No safeguards need to be taken with chaos concurrency because there is either no chance of conflicts or overwrites are ok.  Chaos concurrency is typically used in single-user applications or in multi-user applications where data are segregated in such a way that concurrent updates are either not possible (e.g. unique web session key) or so unlikely (e.g. CustomerID key) that the risk of lower concurrency level isn’t warranted.

The choice between optimistic, pessimistic and chaos involves striking a balance between data availability, integrity, rework and development effort.  It is important to pick the concurrency model appropriate for the task at hand.  Using the wrong model can result in unnecessary blocking, long response times and data problems.  For example, using a chaos model in a multi-user system (which is often done inadvertently simply because concurrency wasn’t considered during development) can lead to lost updates.

Concurrency Control Semantics

The meanings of optimistic vs. pessimistic concurrency control are different depending on whether the context is the application or database server.  Here’s an excerpt from the SQL Server Books Online:

Source: SQL Server 2005 Books Online, Types of Concurrency Control  (http://msdn.microsoft.com/en-us/library/ms189132.aspx)

Now let’s take a look at Types of Concurrency Control in the Visual Studio 2008 .NET documentation:

Visual Studio 2008 .NET, Types of Concurrency Control (http://msdn.microsoft.com/en-us/library/cs6hb8k4.aspx)

Here the table summarizing the VS and SQL 2005 documentation:

Table 2: Application Concurrency Control

You might be surprised to learn that readers block other readers when an application uses pessimistic concurrency control.  I should clarify that “readers block readers” is typically done only when the intent of reading data is a subsequent update (e.g. a user clicks edit) and it is likely that other sessions will try to edit the same data.  This approach ensures no conflicts can occur when data are later saved and prevents data overwrites but at the cost of concurrent data access.

I think some confusion about concurrency stems from the fact that applications may or may not leverage database features in concurrency control implementation.  For example, a pessimistic control application might use database transactions and locking mechanisms to ensure that only one user can edit inventory quantity.  The same pessimistic application could instead serialize data access in the middle tier and perform data access using an optimistic or even chaos model.  Concurrency control implementation details depend much on application architecture; there is not necessarily a right or wrong approach as long as concurrency objectives are met.

My next post in this concurrency model series will discuss how SQL Server transaction isolation levels and row versioning in relate to concurrency control.   I’ll also discuss how applications can leverage these features to maximize concurrency.

Most of us have long been aware that non-parameterized ad-hoc queries can pollute procedure cache with entries that will never be reused.  Each distinct query string is a separate cache entry and, unless the exact query with the same values is executed again, the plan will remain in cache unnecessarily until aged out.

However, I was surprised to learn recently that even parameterized queries can bloat procedure cache if one isn’t careful.  This was brought to by attention by SQL Server MVP Erland Sommarskog and confirmed by fellow MVP Adam Machanic, who experienced this problem at a customer site.

The issue is that parameter declarations are part of cache entry statement text.  Consider the following code:

SqlClient defined the parameter as nvarchar(10) because the supplied string value length was 10 and .NET strings are Unicode.  Below is the resulting statement text from sys.dm_exec_query_plan:

Now run the same code with a different last name value:

You probably guessed it - an additional cache entry was created for the same query due to the different name length:

It is likely that no more than a dozen or so cache entries will exist for this particular query since the only differentiator is the last name length.  However, with queries that contain multiple string parameters of varying lengths, the number of cache entries increases dramatically while the likelihood of reuse diminishes.  In Adam’s case, the main culprit was a string with a large list of values (between 1 and 1000+ items) that was split using a technique described in Erland’s Arrays and Lists articles http://www.sommarskog.se. 

Preventing Procedure Cache Bloat

An easy way to prevent unneeded procedure cache entries is to explicitly specify the maximum data length for string and other variable length values.  In fact, it’s a good idea to specify both the length and SqlDbType to ensure the parameter declaration matches the target column.  Not only will this help prevent cache bloat due to different parameter lengths, the matching data type can avoid the performance gotcha of a non-sargable expression caused by implicit data type conversion.

There are a number techniques to specify the parameter length and SqlDbType in .NET.  For example, the code below uses an overloaded SqlCommand.Parameters.Add method.  If you use AddWithValue, be sure to set the Size and SqlDbType properties of the newly created parameter afterward.

Perhaps the single most important SQL Server application development Best Practice is the consistent use of parameters in application code.  Parameterized database calls are more secure, easier to program and promote query plan reuse.  Yet I continue to see code with SQL statements constructed by concatenating literal strings with variable values.  I think this is largely out of ignorance rather than design so I decided to show how easy it is for .NET application developers to use parameters and peek into the internals to show why parameters are so important.

How to Use Parameters

Below is a C# snippet of a SqlClient query built and executed using variable concatenation.  This is an example of how not to supply variable values to a SQL query.

Here is a parameterized equivalent of the same query.  The only changes are the parameter token in the query string and that the parameter value was added to the parameter collection instead of embedding in the query text:

So all an application developer needs to do is specify parameter tokens in a query string and add values to the parameter collection.  In the case of a parameterized stored procedure call, no tokens are necessary; simply specify the procedure name as the CommandText, specify CommandType.StoredProcedure and add parameters to the collection:

Parameters Under the Covers

The real differences between a parameterized and non-parameterized statement can be gleaned from a SQL and network trace.  Here is the associated trace event captured with SQL Profiler of the non-parameterized code example:

Note the “SQL:BatchCompleted” event.  The SqlClient API simply sent the query to SQL Server as a batch (text containing one or more SQL statements) because the SqlCommand.Parameters collection was empty.

Digging deeper with a tool like Microsoft Network Monitor, we can see the TDS (Tabular Data Stream) protocol data that SqlClient sent to SQL Server for the request:

The TDS packet contains the entire Unicode SQL statement string, including the text value of 1 (Unicode character 0x0031, boxed in blue) that the application passed in the CommandText.

Now let’s take a look at the parameterized query trace event:

The fact that the EventClass shows “RPC:Completed” event instead of “SQL:BatchCompleted” indicates that SqlClient didn’t really wrap the query in sp_executesql to achieve parameterization like the TextData seems to show.  A RPC (Remote Procedure Call) is a specialized type of SQL request in which parameter values are passed separately from the SQL statement in the TDS request and in native form.  The SqlClient API knew to use a RPC instead of a batch because the non-empty parameters collection.

We can see from the dump of the TDS packet that it contains no reference to sp_executesql nor does it contain the string value 1 for the parameter value that the Profiler trace showed (see SQL Trace Parameter values are not always as they seem for more info).

The important point with the RPC request is that the value 1 (binary integer value, boxed in blue) was passed separately from the query text.  This fact has a number of important implications for security, performance and programming. The difference between the TDS packets shows the essence of parameters in communication between the application code and SQL Server. 

Use Parameters for Security

If you don’t know what SQL injection is, Google is your friend.  Basically, injection vulnerability allows a user to manipulate the intended SQL by supplying mal-formed values.  Injection can allow a malicious user to retrieve sensitive data or change database information, limited only by the database security context.

Parameterized calls protect against SQL injection because the executed SQL statement is constant regardless of the supplied values.  As long as no dynamic SQL is constructed, the intended SQL statement cannot be manipulated.  An application developer only needs to ensure that the desired parameter values can be passed (e.g. key value from session state instead of from http query string) to guard against unintended data disclosure or manipulation.  Using stored procedures (with CommandType.StoredProcedure) always results in an RPC call and provides an additional security layer.

Use Parameters for Performance

Parameterized statements are cached to facilitate reuse and avoid compilation overhead, which is especially important in high-volume OLTP environments.  Furthermore, using stored procedures further promotes caching and reuse. 

The only downside to parameters from a performance perspective is that a given plan may be optimal for some values but not others.  In cases where sub-optimal cached plans are an issue, techniques such as RECOMPILE hints or compromise plan guides can help avoid a sub-optimal execution plans due to varying parameter values.  See article Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 for more information.

Use Parameters for Cleaner Code

Parameters are easier to program and provide cleaner code because there is no need to escape (double-up) quotes, format datetime strings or use “.” as a decimal separator.   Remember that the API takes care of passing parameter values in native form so string formatting is not needed when parameters are used.

Ownership chaining is one of my favorite SQL Server security features.  I like ownership chaining because it allows me to confine data access to specific stored procedures, views and functions while preventing direct access to the underlying tables.  This approach provides a well-defined application interface that insulates applications from the physical implementation and allows me to change things on the back-end as long as I don’t change the interface.  Also, thanks to ownership chaining, I have many options to vertically and horizontally partition data to meet security objectives while ensuring tables are not accessed directly.  

Ignorance is not bliss when it comes to ownership chaining security, though.  I’ve seen cases where data access was inadvertently allowed because ownership chaining wasn’t considered.  It is imperative that SQL Server DBAs understand ownership chaining in the SQL Server security model so that data is properly secured.  I want to point out a couple of important security concepts as it relates to ownership chaining with the hope that it will help SQL Server DBAs implement a security scheme that is appropriate for their environment and also easy to manage.

Ownership Chain Behavior

SQL Server always checks permissions on directly referenced objects using the current connection security context.  However, permissions are evaluated differently when objects are accessed indirectly, such as those used in a trigger, view or stored procedure.  The behavior of ownership chaining is that object permissions are not evaluated when the calling and referenced object owners are the same.  Also, because object permission checking is short-circuited in an unbroken chain, ownership chaining takes precedence over an impersonated security context (EXECUTE AS clause) as well as denied object permissions.   Consequently, permissions on indirectly referenced objects are irrelevant in an unbroken ownership chain.  This ownership chaining behavior gives a DBA control over exactly who can use which objects while preventing ad-hoc access to other objects.

Ownership chaining behavior might not be intuitive at first.  I like to think of it from the perspective that the purpose of granting object permissions is so the object can actually be used.  For example, it is of no use to grant a user SELECT permissions on a view unless data can be retrieved from the view.  The same applies even when a stored procedure or function impersonates with EXECUTE AS; the impersonated principal must be able to use objects referenced by the proc in order for the procedure to be useful.  I keep in mind that with ownership chaining, I effectively grant permissions on exposed object functionality regardless of how the functionality is implemented.

Ownership chaining is limited to a single owner in order to prevent escalation of privileges in environments where non-privileged users can create objects.  Ownership chaining also does not apply to dynamic SQL executed from a stored procedure; the invoking (or impersonated) user needs permissions on objects referenced by dynamic SQL statements.  I should add that a one of the useful purposes of EXECUTE AS and signing modules with certificates is to provide needed permissions in a broken ownership chain or granting statement permissions without granting user permissions directly.  See SQL Server MVP Erland Sommarskog’s article Giving Permissions through Stored Procedures for a discussion of these techniques.

Ownership Best Practices

Ownership is obviously central to ownership chaining.  In the vast majority of environments, it’s probably best to simply inherit the owner (a.k.a. AUTHORIZATION) from the object’s schema instead of overriding on individual objects.  It is possible to assign ownership to individual objects (using ALTER AUTHORIZATION) in order to implement chaining that is independent of schema but I think this is overly complex for most environments.  One reason I can think of why one might specify a different owner at the object level is in the case where a table contains particularly sensitive data and one wants to deliberately prevent inadvertent access via ownership chaining.  I think such cases are rare, though. 

I described some of the practices I follow for schema and object ownership in Keep Schema and Ownership Simple.  In summary, I specify dbo as the schema authorization (owner) unless I want to break the ownership chain between schemas to create a security boundary.  When a security boundary is desired, I create a role (or user without login) with the same name as the schema for the schema authorization.

I like to keep things simple because simplicity is easier to manage and less prone to error.  When I’m faced with schema design decisions, I pick the selection with the least complexity that meets my objectives.  Here are some of my thoughts regarding schema and ownership in SQL 2005.

Schema

A schema is basically a container that categorizes database objects and simplifies security administration.  As a namespace, schemas logically organize objects without the need for special object naming rules.  Different objects can have the same name as long as they exist in different schemas because the schema name is essentially an extension of the object name that will “uniqueify” the name within a database. 

Categorizing objects by schema is particularly useful in complex databases with many objects.  There is some subjectivity on exactly how one might draw schema boundaries but the basic concept is the same; group related objects into different schema to provide organization to an otherwise unwieldy schema.  Classifying related objects by schema makes complex databases easier to understand and manage.

Schema also simplifies security administration because permissions can be granted en mass at the schema level.  For example, I can grant EXECUTE permissions on all objects in a schema with a single statement like “GRANT EXECUTE ON SCHEMA::Sales TO SalesRole”.  I can grant CONTROL permissions on a schema to allow privileged users to full control over a specific schema but not others in the same database.

Even with the option to use multiple schemas, I tend to use the built-in dbo schema.  I do this because most of the applications I maintain were developed before SQL 2005 and all objects are already in the dbo schema.  Some of those legacy systems could benefit from multiple schemas but I’ll continue to use dbo for those applications to be consistent until I need to add a group of new objects that are appropriate for a separate schema.  The new SQL 2005 databases I’ve developed thus far have been fairly simple and haven’t warranted using multiple schemas for either classification or security purposes.

Ownership

The owner is important for two main reasons:  1) the owner has powerful CONTROL permissions over all owned objects and 2) the owner determines whether or not the ownership chain is broken between objects.  Schema-contained objects will inherit the schema owner unless explicitly overridden using ALTER AUTHORIZATION.  Personally, I think it best for objects to inherit the schema owner in the vast majority of cases; if an object warrants a different owner than the schema, the object probably belongs in a different schema.

I use the built-in dbo principal for ownership unless I have a reason to do otherwise.  This approach is perfect in environments where only db-owner role members can create objects and schemas are used solely as a namespace rather than a security boundary.  The dbo principal exists in all databases so there is no a need to create a user or role for ownership purposes.  Simple is good.

Different schema owners provide a security boundary between objects in different schema because this breaks the ownership chain.  With a broken chain, explicit permissions on indirectly referenced objects are needed by the end user or the impersonated principal.  Different schema owners ensure that I don’t inadvertently provide access to data in different schema via ownership chaining.

Note that an owner can be any database principal and does not necessarily need to be associated with a login.  I find this feature especially handy in situations where I want to specify an owner other than dbo.  Since I can specify an owner that is not a real person, I won’t need to change ownership if the owner leaves the company or moves on to other roles in the organization. 

It’s probably best to create a principal (role or a user without login) with the same name as the schema for ownership purposes when different owners are desired.  The only case I can think of where it might be appropriate for a real person to own a schema (at least initially) is in a development environment when non-dbo users might create schemas and objects.

Do you know who owns your databases?  Execute sp_helpdb on your SQL Server instances and you might find some surprises under the “owner” column.  It isn’t uncommon to see accounts of people who have left the company or moved on to other roles in the organization that don’t require privileged database access.  Yet these owners still have full database permissions, including the ability to drop the database.  To prevent these security issues and other problems, consider establishing an appropriate database ownership standard for your environments.

Database ownership is an often forgotten detail because it is implicitly set to the database creator’s account.  The owner will initially be a Windows account or SQL login, depending on the authentication method used by the creator.  Note that the owner is always an individual account, not a group or role, so a database created by a sysadmin role member is actually owned by the creator’s individual account instead of a built-in security principal (unless the creator logged in using the “sa” account).

A Best Practice is to change the database owner immediately after creating, restoring or attaching a database.  Unless I have a reason to do otherwise, I specify “sa” as the database owner.  This can be done with sp_changedbowner in SQL 2000 or with ALTER AUTHORIZATION in SQL 2005:

The Significance of the Database Owner

Database ownership is important from a security perspective because the owner account is mapped to the built-in “dbo” user.   The “dbo” user, sysadmin role members and db_owner role members all have full database permissions and can also DROP the database.  The database owner is also used as the authorization of the “dbo” schema, which comes into play with ownership chaining.  With cross-database chaining, the databases involved must have the same owner in order to provide an unbroken chain for “dbo” schema objects.

A difference between the database owner and db_owner role members is that there is exactly one “dbo” user (the database owner) but there may be many users that are db_owner role members.  The owner’s account cannot be explicitly added to the database because the owner is already implicitly mapped to the “dbo” user and an account can be mapped to no more than one user per database.  If you attempt to add the owner as a database user, error message “The proposed new database owner is already a user or aliased in the database” results.

Troubleshooting Database Ownership

The database owner is ultimately identified by the account SID (security identifier).  The creator’s account SID is recorded in 2 places:  1)  at the server level in sys.databases/sysdatabases and  2)  in the database as the dbo user SID in sys.database_principals/sysusers.  These SIDs will normally match but can get out-of-sync following a database restore or attach.  You will also end up with a NULL database owner if the owner’s Windows account is deleted because of the orphaned SID.

Mismatched owner SIDs can result in problems such as

·         Problems executing system stored procedures

·         Problems with tools

·         Broken cross-database ownership chains

The sample queries below will help identify problem database owners.  Problem owners will have mismatched SIDs and/or NULL owner names.  Fortunately, problem database ownership is easy to remedy.  Simply change the database owner using sp_changedbowner or ALTER AUTHORIZATION as you would after creating a new database.  In some cases, you might get an erroneous “The proposed new database owner is already a user or aliased in the database” due to the mismatch.  A workaround in this situation is to temporarily change the database owner to a non-conflicting login and then back to the desired owner.