Specifying Locking Hints in a SQL Server Database

[ Team LiB ] Recipe 6.14 Specifying Locking Hints in a SQL Server Database Problem You need to pessimistically lock rows in an underlying SQL Server database. Solution Use SQL Server locking hints from ADO.NET. The sample code contains three event handlers
Nội dung trích xuất từ tài liệu:
Specifying Locking Hints in a SQL Server Database[ Team LiB ]Recipe 6.14 Specifying Locking Hints in a SQL Server DatabaseProblemYou need to pessimistically lock rows in an underlying SQL Server database.SolutionUse SQL Server locking hints from ADO.NET.The sample code contains three event handlers:Start Tran Button.Click Creates a SQL SELECT statement to retrieve the Orders table from the Northwind database. A locking hint, either UPDLOCK or HOLDLOCK, is added to the statement as specified. A Connection is opened and a Transaction started on it with an isolation level of ReadCommitted. A DataAdapter is used on the transacted connection to fill a DataTable. A CommandBuilder is created to generate updating logic. The default view of the table is bound to the data grid on the form.Cancel Button.Click Clears the data grid, rolls back the transaction, and closes the connection.Form.Closing Rolls back the transaction if it exists and closes the connection.The C# code is shown in Example 6-39.Example 6-39. File: UsingLockingHintsForPessimisticLockingForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Data;using System.Data.SqlClient;private SqlConnection conn;private SqlTransaction tran;// . . .private void startButton_Click(object sender, System.EventArgs e){ startButton.Enabled = false; String sqlText = SELECT * FROM Orders WITH ; // Add pessimistic locking as specified by user. if(updLockRadioButton.Checked) sqlText += (UPDLOCK); else if(holdLockRadioButton.Checked) sqlText += (HOLDLOCK); // Create connection. conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]); conn.Open( ); // Start the transaction. tran = conn.BeginTransaction(IsolationLevel.ReadCommitted); // Create the command. SqlCommand cmd = new SqlCommand(sqlText, conn, tran); // Create the DataAdapter and CommandBuilder. SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(da); // Fill table using the DataAdapter. DataTable dt = new DataTable( ); da.Fill(dt); // Bind the default view of the table to the grid. dataGrid.DataSource = dt.DefaultView; cancelButton.Enabled = true; dataGrid.ReadOnly = false;}private void cancelButton_Click(object sender, System.EventArgs e){ cancelButton.Enabled = false; // Unbind the table from the grid. dataGrid.DataSource = null; // Roll back the transaction and close the connection. tran.Rollback( ); conn.Close( ); startButton.Enabled = true;}DiscussionA lock is an object indicating that a user has a dependency on a resource. Locks ensuretransactional integrity and database consistency by preventing other users from changingdata being read by a user and preventing users from reading data being changed by auser. Locks are acquired and released by user actions; they are managed internally bydatabase software.A locking hint can be specified with SELECT, INSERT, DELETE, and UPDATEstatements to instruct SQL Server as to the type of lock to use. You can use locking hintswhen you need control over locks acquired on objects. The SQL Server Optimizerautomatically determines correct locking; hints should be used only when necessary.Locking hints override the current transaction isolation level for the session.A locking hint is specified following the FROM clause using a WITH clause. The hint isspecified within parentheses and multiple hints are separated by commas.Tables Table 6-21, Table 6-22, and Table 6-23 describe the different locking hints thatyou can use, categorized according to their function. Table 6-21. SQL Server locking hints for isolation level Locking hint Description Hold a shared lock until the transaction is completed insteadHOLDLOCK of releasing it as soon as the required object—table, row, or data page—is no longer needed. Do not issue shared locks and do not recognize exclusiveNOLOCK locks. Applies only to the SELECT statement. Use the same locking as a transaction with an isolation levelREADCOMMITTED of READ COMMITTED.READUNCOMMITTED Same as NOLOCK. Use the same locking as a transaction with an isolation levelREPEATABLEREAD of REPEATABLE READ. Use the same locking as a transaction with an isolation levelSERIALIZABLE of SERIALIZABLE. Table 6-22. SQL Server locking hints for granularity Locking Description hint Do not issue shared locks and do not recognize exclusive locks. AppliesNOLOCK only to the SELECT statement.PAGLOCK Use page locks where a single table lock would normally be used.ROWLOCK Use row-level locking instead of page-level and table-level locking. Use table-level locking instead of row-level and page-level locking. ByTABLOCK default, the lock is held until the end of the statement. Use an exclusive table lock preventing other users from reading orTABLOCKX updating the table. By default, the lock is held until the end of the statement. Table 6-23. SQL Server Locking Hints for Other Functions Locking Description hint Skip locked rows that would ordinarily appear in the result set rather than blocking the transaction by waiting for other transactions to release locksREADP ...