|
|
|
|
Locking is a tradeoff between concurrency and the amount of resources available. In most transaction processing systems you try to have as few concurrency problems as you can because the obvious job of your system is to collect data. It is difficult to predict all different types of concurrency problems that an application might have; however, some of them are fairly common. Often concurrency problems can be resolved by better application design or improving the logical and physical database models. However, many times redesigning the application is not an option - you only have an opportunity to tune the transactions and locking on the database code level.
Several types of common concurrency problems are described below:
This problem is somewhat difficult to identify because the system stored procedures used to troubleshoot the locking issues will show you a normal number of exclusive page locks. To troubleshoot this scenario, go back and examine a number of records in the table, as well as the row size. If the number of records is fairly small, chances are they all reside on the same 8Kb page.
There are a couple of ways to resolve this issue. First, you can reduce the fillfactor setting on the table's clustered index. This setting determines the amount of free space to be left on a page when the index is created. Reducing the fillfactor will leave more free space on the page and force SQL Server to allocate additional pages for the table. Second, you can increase the row size (or record size). This can be done by adding dummy columns to the table - this technique is commonly referred to as row padding. Row padding will artificially increase the row size thereby decreasing the total number of rows that can be stored on a single page. With SQL Server 2000, you can use the ALTER TABLE statement to add and remove columns to or from a table. So if row padding does not help, you can always drop the dummy columns without going through several hoops as you had to with previous versions. Prior to SQL Server 7.0, you had to copy data out with BCP, drop the table, recreate the table and then reload data back into the table to get rid of the unneeded columns.
With previous releases of SQL Server you could modify the lock escalation threshold to a higher number, thereby advising SQL Server to allocate more page-level locks before escalating to table-level locks. As of SQL Server 2000, locking is managed dynamically. If you encounter too many table locks, chances are your server can benefit from additional memory. The memory allocation is also managed dynamically. However, you can specify the maximum level of memory that SQL Server can use. Many smaller organizations try to "save" money on hardware and attempt to run multiple applications on the same server. When the number of users grows rapidly, they will quickly realize the drawback of this "approach". As a rule of thumb, the database server should not be shared with any other service (such as mail server, backup server, etc). In other organizations dollars are usually not an issue, however, they are prone to lack of planning. In either case, if you have a lock escalation issue, consider increasing the amount of memory available to SQL Server.
AS of SQL Server 2000, full row-level locking is supported, so ideally you should not see this problem. However, if you do experience it, move the clustered index to another column. If you don't have a clustered index, then add one. Keep in mind that the clustered index has to be unique. SQL Server will let you create a clustered index on a non-unique column, but will add a hidden column to the table to warrant uniqueness, which in turn will add to the overhead of every operation. The point to remember is that SQL Server physically arranges data in the table according to the way the clustered key is sorted. Therefore, having a clustered index on a monotonically increasing key does not really speed up your application - looking up a numeric value in a table is usually not time consuming, especially compared to looking up strings. So if your application needs to be optimized to search by a string value such as combination of employee last and first names, then it's beneficial to put a clustered index on a combination of these columns.
Discussion of the type of locking issues you might encounter in a transactional application was based on the default behavior of SQL Server. Next, you need to learn how to alter the default behavior if needed. However, be aware that changing the default behavior might result in one or more of the following problems:
Dirty reads occur when a transaction modifies the data that has not been committed, that is, a transaction attempts to modify a piece of data that is being modified by another transaction. Suppose a clerk is taking an order over the phone from Mr. Brown. The clerk adjusts Mr. Brown's balance with the amount of purchase - $120.00. While this is happening Mrs. Brown places an order on the web site for $150.00 worth of merchandise. Since you allowed dirty reads, Browns' balance is now $270.00 above their beginning balance. But just as Mr. Brown was about to get off the phone he cancelled the purchase. The clerk appropriately hit the cancel button on her screen; however, the balance remains equal to the beginning balance plus $270.00. The Browns will not be very happy when they receive their bill next month since they only placed orders for $150.00, but you charged them $270.00.
In other words, according to the default behavior of SQL Server, Mrs. Brown's transaction must wait until Mr. Brown is completely finished with his transaction.
These happen if you let two transactions modify the data at the same time and the transacton that completes earlier is lost. Continuing the previous example, if Mr. Brown makes a purchase of $120.00 and Mrs. Brown makes the purchase of $150.00 and both transactions are initiated at the same time, they will both update the same beginning balance. Suppose the beginning balance was $300.00. Mrs. Brown happen to stay on the web site a bit longer and placed the order for $150. Well, next month the Browns will be happy to see their balance being only $300 + $150 = $450, however, they won't receive the merchandise ordered by Mr. Brown because it was lost, or overwritten, by Mrs. Brown's transaction.
Non-repeatable reads occur if a transaction is able to read the same row multiple times, but gets a different value each time. Suppose Mrs. Brown is very considerate of the family budget and will only place the order for the amount that will make their balance less than $450. While Mr. Brown is on the phone with the clerk ordering some merchandise, thereby increasing the Browns' balance, Mrs. Brown is checking the balance. Well, the first time she checked the balance was $300. Now only seconds later, after she found the shirt she liked, she checks the balance again and it says $420. A minute later, she checks it again, just to make sure she's not crazy and the balance says $450. She picks up the phone and calls the customer service department. Since Mr. Brown decided to cancel the purchase the customer rep tells Mrs. Brown that the balance is $300. So, which one is it?
Phantoms occur due to a transaction being able to read a row on the first read, but not being able to modify the same row due to another transaction inserting or deleting rows from the same table. A phantom read could be illustrated by the same example where Mrs. Brown calls the customer service department. The first time the clerk checks the sales table, Mr. Brown has purchased some merchandise on the day Mrs. Brown called and everything is in order. However, if Mr. Brown independently cancels the order seconds after the clerk pulled up the sales data, the balance will show the original $300.00 balance and no purchase on that day for any subsequent refreshes by the sales clerk.
To deal with all the problems mentioned above, SQL Server provides various isolation levels for transactions. Recall that one of the ACID properties of transactions is being Isolated - not affected by any other transactions. The transaction isolation level determines the degree to which you wish your transaction to conform to this rule. The following table discusses each isolation level available with SQL Server and the types of reads that they allow:
SQL Server 2005 introduces two new transaction isolation levels called SNAPSHOT ISOLATION and READ_COMMITTED_SNAPSOT. With these isolation levels, data writers don't block data readers and vise versa. This is due to how SQL Server manages data under SNAPSHOT ISOLATION levels, also referred to as row versioning isolation levels. SQL Server effectively versions data rows in tempdb system database before an explicit transaction is started. Data readers will read the row version from tempdb database, whereas data writers modify the data in the user database where it resides.
READ COMMITTED SNAPSHOT ISOLATION level reads the version of the data immediately before the update transaction began. This isolation level doesn't detect any update conflicts. It places UPDATE locks on any data rows to be modified by transactions in the user database and reads data from tempdb. READ_COMMITTED SNAPSHOT isolation level is set by turning on database option READ_COMMITTED_SNAPSHOT as follows:
ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON GO
READ_COMMITTED_SNAPSHOT isolation consumes fewer resources in tempdb database than SNAPSHOT ISOLATION. Furthermore it supports distributed transactions whereas SNAPSHOT ISOLATION does not. Yet another advantage of READ_COMMITTED_SNAPSHOT isolation is that it requires no changes to the existing applications. Database administrators simply have to set the database option.
SNAPSHOT ISOLATION level, on the other hand, requires setting the isolation level explicitly before beginning a transaction. This isolation level attempts to issue an exclusive lock on the rows in tempdb database as well as in the user database. This level detects if changes have been made to the data rows since they were versioned in tempdb. If a change is detected the transaction is immediately rolled back.
In order to set READ COMMITTED SNAPSHOT isolation, your connection must be the only connection to the current database; but you don't have to put the database in single-user mode before allowing this isolation level. READ COMMITTED snapshot doesn't require shared locks, but reads the committed data. So if a transaction has modified the data, the SELECT statement immediately following this transaction will retrieve the new (up-to-date) version of the row. On the other hand the SNAPSHOT isolation requires shared locks but retrieves the version of the row before the update took place.
Since row versioning isolation levels make heavy use of TEMPDB database, you need to ensure that this database has plenty of room to grow. Despite the fact that row versioning isolation levels require additional resources, your system might still perform better than with the default isolation level (READ COMMITTED). This is because row versioning isolation levels require fewer locks and allow more concurrent transactions.
