推荐订阅源

最近在准备70-315考试时遇到这样一道问题：
You are creating an ASP.NET accounting application that stores and manipulates data in a
Microsoft SQL Server database named TestKingSrv. One of the pages in the application will
be used for performing month-end operations to calculate the balance of all accounts.
When a user clicks a button on the page, you want your code to run several stored procedures
to calculate the month-end balances. These procedures must all succeed before the calculated
balances can be stored in the database. If any of the procedures fail, then you do not want to
store any of the month-end calculated balances. While the procedures are running, you do not
want any users to be able to edit, add, or delete data in the tables affected by the procedures.
What should you do?

A. Create a class derived from System.EnterpriseServices.ServicesComponent to run the stored
procedures.
Annotate the class by using a TransactionAttribute type of attribute.
Set the Value property of the attribute to TransactionOption.RequiresNew.

B. Create a master stored procedure.
Use this master stored procedure to call the other stored procedures that perform the monthend
operations.
Add WITH REPEATABLEREAD to the master stored procedure.

C. Use structured exception handling to catch a SqlException if one of the stored procedures
fails.
Use the Procedure property of the SqlException to identify which stored procedure generated
the exception, and call a stored procedure to reserve the previous calculations.

D. Set the IsolationLevel property of a SqlTransaction object to IsolationLevel.Serializable.
Assign the SqlTransaction object to the Transaction property of the SqlCommand object.
Use a SqlCommand object to run the stored procedures.

这道题目的关键点在于 

" While the procedures are running, you do not
want any users to be able to it, add, or delete data in the tables affected by the procedures."
其实就是SQL SERVER IsolationLevel 的问题：
The available values for SQL Server 2000 are:

1. ReadUncommitted – This is, essentially, no isolation. Anyone can read the data placed in a table or updated immediately after the SQL statement causes the change – no commit is required. This could lead to a process having out-of-date data: it may be using a version of the data that was then rolled back out of the table!
2. ReadCommitted – This is slightly more isolated. In this case, a transaction can only read data from the table that has already been committed. When a transaction wants to update data, it acquires a shared lock on that data and (if successful getting the lock) updates the data. Transactions outside of that transaction cannot update the data in that table until the locking transaction commits. This is only slightly more isolated, however: a SQL statement executed twice within a transaction could return a different result-set if a second transaction changes and commits the data the SQL statement executes on between the two statements. This is the default isolation level for SqlTransaction.
3. RepeatableRead – Slowly getting more isolated. In this case, a shared lock is applied on all data queried within a transaction. This means that no other transaction can alter the data used in your transaction. This prevents the case where data you had queried once changes on subsequent queries. It does not, though, prevent the case where rows are added to the table that may be returned in subsequent queries.
4. Serializable – Locks are placed on ranges of the tables you are using, preventing other users from changing your data or adding new rows underneath you. This is the most isolated isolation level, but it does come with the drawback of locking more data than your transaction may strictly need.

In SQL Server 2005, a new isolation level will be added: snapshot isolation. In snapshot isolation, rows are versioned once they are accessed in a transaction. This essentially means that once a transaction accesses a set of values, they are guaranteed to remain the same until you commit or rollback the transaction. Other transactions starting in the middle of the first will get a ‘copy’ of the original database to operate on. Before any transaction commits, though, SQL Server will test to ensure that the original data they were operating on is the same as the current data in the database. If this is the case, the transaction will commit. Otherwise, the transaction will be rolled back and the user will have to try the batch once again.