I am hoping you might have an answer to a problem we've run into in our student information system development project involving SQL Server lock escalation. The problem is described below. Basically, we are trying to run a batch scheduler process (written in C++). When a school has set up all the elements needed to schedule its students into classes for the school year, it runs the scheduler. The scheduler attempts to put row-level locks on just the data in the relevant tables for the specific school and school year, leaving other schools and users free to continue working. Problem is, SQL escalates the lock to a table lock, which keeps other schools and users from working while the scheduler runs.

We've looked at a number of documentation resources and so far, the best explanation of what is happening is that at some point, SQL Server 2000 determines that the resources required to manage the locks on database data related to the scheduler process will degrade performance so much that SQL Server decides to escalate the lock to the table level. The documentation we've read through so far suggests the escalations will happen and that there in no way to override the escalation process. We are discussing alternative solutions, but would like to know if it is true that there is no way to manage the SQL Server locking escalation process ourselves. If you have a moment, please take a look at the problem description below.

Question: Is it possible to override or manage the automatic lock escalation process in SQL Server 2k, like through hints or some other mechanism?

Problem: While trying to lock just a “school & year” slice of a table, SQL Server locks the complete table. In the SchoolCourse table, the batch scheduler needs to lock all courses for a specific school and year. (Other tables also apply, but testing was done on this table.)

Test #1: Set Transaction Isolation Level Serializable Go Begin Tran Select * from SchoolYearInfo (XLock) where SchNum = 411 and SchYear = 20022003 Select * from SchoolCourse with (index = IX_SchoolCourse, XLock) where SchNum = 411 and SchYear = 20022003

While the above transaction was in effect, a different session was able to access any other SchoolYearInfo row other than the one locked. However, that session wasn’t able to access any row in the SchoolCourse table.

Test #2: Set Transaction Isolation Level Serializable Go Begin Tran Select * from SchoolYearInfo (XLock) where SchNum = 411 and SchYear = 20022003 Select * from SchoolCourse (XLock) where SchNum = 411 and SchYear = 20022003 and (CrsNum > 2599 and CrsNum < 2605)

When using the above transaction, the only SchoolCourse rows that were locked were CrsNum 2600 thru 2604. The second session was able to access other rows in the table, with the exception of the “locked” rows.

Conclusion: In Test #1, according to 3rd party documentation (see attached), the row level locks were “escalated” to a table level lock due to the quantity of the locks. There isn’t any control as to when a locking level is escalated. It is determined by SQL Server 2000.

This table level lock will exclude all schools from accessing any course rows while the scheduler is running. This is not an acceptable mode of operation.

Possible Solution #1: We could implement a locking strategy of our own using a database table. Each application would have to query the table to determine what is locked. This would include all ColdFusion screens & reports. I don’t like this solution because it is very easy to break. If an application doesn’t use the locking table, then this approach is broken.

Possible Solution #2: Have a separate scheduling database, similar to the current Pupil/Resc implementation. The major difference would be that both databases would have the exact same design. The scheduling database would be populated with the new students to be scheduled, then after all batch processing activity was completed, moved into the transactional database. Since both databases are identical, all reports and screens would function within either database.

To make this implementation invisible to the user: A database table containing school number, year, and database path would be used by each application to determine where to get data from. When a school and school year was selected from a dropdown list, the table would be queried to determine the connection string and the proper database would be accessed.

It has already been determined that the batch and interactive scheduler cannot be operated concurrently. That is, a school must use the batch scheduler until they are comfortable with the results, then when they switch to using the interactive scheduler they can’t go back to the batch scheduler. This is due to class counts. Therefore, when a school is completed with its’ batch scheduling, the scheduling database information would be moved into the transactional database.

-- J. Michael Brown (jbrownaa@edcenter.egusd.k12.ca.us), February 24, 2003