Locking Choices
After you choose a cursor type, you need to choose how ADO will lock a row when you make a change. Table 2 shows the options available for each type of cursor when you use the SQLOLEDB or MSDASQL providers. In making this choice, you aren't specifying the kind of lock; you're specifying when SQL Server locks the row. When you tell ADO to make an update, SQL Server places an exclusive lock on the page (or the row in SQL Server 7.0).
Read-Only Locking. The read-only locking option doesn't use a lock at all. This option tells ADO that you won't make any changes to the rows in the cursor. At most, SQL Server will place shared locks on the rows as it reads them and release the locks after it populates the cursor. Because applications don't usually change data through a cursor, read-only locking is the default setting.
Pessimistic Locking. Pessimistic locking assumes that your changes will collide with another user's. This option tells ADO to get an exclusive lock on the row when you make any change to any column in the record. The problem the pessimistic locking option presents is that the row stays locked until you commit your changes by calling the Update method. If your update occurs in the middle of a transaction, then the lock remains until the transaction ends. Therefore, don't wait for user input after you make changes to the row, especially if you're using SQL Server 6.5, which locks the entire page and not just the row. Instead, make your changes in the fastest possible way, then call the Update method to update the row and release the lock.
Optimistic Locking. To offset the problems of pessimistic locking, ADO offers the optimistic locking options, Optimistic and BatchOptimistic. These options assume that collisions are unlikely; therefore, ADO gets the exclusive lock only after you call the Update or UpdateBatch method. The Optimistic option lets you work with one row at a time, whereas the BatchOptimistic option lets you make multiple updates within the cursor, then save them all at once. When you call the UpdateBatch method, ADO tries to update each changed row as a separate, independent update.
No implied transaction occurs with the BatchOptimistic option. Some updates can succeed; others fail. If a row's update fails for any reason, ADO sets the Recordset's Status property for that row to a value that identifies the problem, then continues processing. When the UpdateBatch method finishes, you can set the Recordset's Filter property to adFilterConflictingRecords, which forces the Recordset to show only the records whose updates failed.
When a Cursor Is Not a Cursor
In SQL Server 7.0, a cursor isn't a cursor if you ask for a server-hosted, forward-only, read-only cursor. This type of cursor, called a firehose cursor, prompts SQL Server 7.0 to use its Default Result Set to stream records to the client. SQL Server 7.0 fills network packets with rows as soon as it can and sends the network packet to the client. The client's networking software buffers the packets until ADO is ready to present the rows to the application. SQL Server uses the networking subsystem as a data buffer so that it can send rows as fast as possible.
SQL Server also uses Default Result Sets when you execute a stored procedure that returns records. The Command object and the Recordset object create a forward-only, read-only recordset. The Execute method of the Connection object always returns a forward-only, read-only recordset, and because the Connection object's default is to use server-hosted cursors, the commands that the Execute method sends will always return Default Result Sets.
The firehose cursor approach assumes that the client will retrieve all rows from the cursor and won't change any of them; therefore, SQL Server can send all rows at once. This approach eliminates the roundtrips across the network that ADO makes when it needs to fetch another row, which means this kind of cursor uses the least amount of network and server-side resources and uses a minimal amount of virtual memory on the client.
How to Choose a Cursor
Your choice of cursor depends on how your application will use the data. Table 3 shows which cursor types are available for client-hosted and server-hosted cursors. When you need data to fill a listbox or to create a report, choose the firehose cursor. In this case, the firehose cursor will yield the best performance because your application will receive records as soon as SQL Server 7.0 can fill a network packet. An additional benefit of the firehose cursor is that your application will be able to process records and receive new records simultaneously because the networking subsystems in Windows NT and Windows 95/98 will receive network packets in parallel with your application's execution.
The one limitation of the firehose cursor is that SQL Server allows only one operation on the connection at a time. Because SQL Server 7.0 streams records with no fetch commands from the client, it imposes the restriction that any new operations must wait until all the rows in the firehose cursor have been sent to the client. Therefore, you can't execute an UPDATE, INSERT, or DELETE while you are reading rows from a firehose cursor. No other cursor has this restriction.
Another common type of application is one with a data-entry form that contains data from only one record and is updated when the user moves to another record. For this kind of application, you can use either a client-hosted static cursor with optimistic locking or a server-hosted keyset cursor with pessimistic locking. The benefit of pessimistic locking is that SQL Server guarantees that transactions involving the record will succeed. Transactions fail only if deadlocks occur.
If you use pessimistic locking, write your code so that records are locked no longer than necessary. The worst-case scenario is one in which a user opens a record and makes a change but doesn't save the entire record. Then the user goes to lunch. You can use timers and automatic timeouts to handle such situations, but the best practice is to use pessimistic locking only when a record needs to be isolated from actions by other users. In all other cases, use optimistic locking.
Likewise, if you use data-bound objects in Visual Basic 6.0, don't use pessimistic locking because ADO will lock the record the moment you change one byte. Use optimistic locking for data-bound objects. The best choice is a client-hosted static cursor, although server-hosted keyset cursors work well for applications that share data changes with the other users.
Similarly, for applications that use grid controls, a client-hosted static cursor or a server-hosted keyset cursor works best. Both of these cursors let the application move freely within the cursor; therefore, the choice depends on how many rows the user will visit and how many rows will incur changes. If the user will visit at least 50 percent of rows, it makes sense to cache the data on the local machine. If the user will jump around within the cursor, the keyset cursor offers the advantage of retrieving the complete row contents only when the application requests them. The keyset cursor also retrieves the most up-to-date version of the row, which is useful when the user must make decisions based on information that multiple concurrent users can modify.
For situations in which a user can make many changes and then decide to cancel all of them at once (e.g., by clicking on the Cancel button of an order entry form), BatchOptimistic locking restores data integrity and consistency. SQL Server doesn't send the changes to the server until the program calls the UpdateBatch method. Therefore, you can simply close the Recordset to discard changes. BatchOptimistic locking also eliminates the network roundtrips that occur with Optimistic locking when users make changes to one record, then move to another. For example, in a master-detail application such as order entry, users often modify many rows at the same time. Those modifications are single-row updates, but they are logically grouped into one entity. BatchOptimistic locking lets the user make all the entries at one time and submit them as a single operation. This capability is especially important with data-bound controls.
Choosing the right cursor comes down to two factors: where will the cursor data reside, and does the application need to see changes in the rowset while the cursor is open? After you answer those questions, the choice of cursor is easy. Figure 1 lists some of the trade-offs between client-hosted and server-hosted cursors.
To see options available for the SQLOLEDB and MSDASQL providers, go to http://www.MCSDonLine .com, which posts a VB application that demonstrates every possible combination of cursor location, cursor type, and locking type. Knowing which types of cursors are available will help you in the decision-making process. And if you use ADO, you can change your mind about your cursor choice without having to change a lot of code.
End of Article
Prev. page
1
[2]
next page -->
