关于 游标 锁

在使用ADO操作Sql Server时，老是出现错误，于是将游标和锁的内容复习了一下。

What is a Cursor?

Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially those that are interactive and online, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

A cursor is implemented by a cursor library. A cursor library is software, often implemented as a part of a database system or a data access API, that is used to manage attributes of data returned from a data source (a result set). These attributes include concurrency management, position in the result set, number of rows returned, and whether or not you can move forward and/or backward through the result set (scrollability).

A cursor keeps track of the position in the result set, and allows you to perform multiple operations row by row against a result set, with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the databases. The cursor is so named because it indicates the current position in the result set, just as the cursor on a computer screen indicates current position.

It is important to become familiar with the concept of cursors before moving on to learn the specifics of their usage in ADO.

Using cursors, you can:

• Specify positioning at specific rows in the result set.
• Retrieve one row or a block of rows based on the current result set position.
• Modify data in the rows at the current position in the result set.
• Define different levels of sensitivity to data changes made by other users.

For example, consider an application that displays a list of available products to a potential buyer. The buyer scrolls through the list to see product details and cost, and finally selects a product for purchase. Additional scrolling and selection occurs for the remainder of the list. As far as the buyer is concerned, the products appear one at a time, but the application uses a scrollable cursor to browse up and down through the result set.

You can use cursors in a variety of ways:

• With no rows at all.
• With some or all of the rows in a single table.
• With some or all of the rows from logically joined tables.
• As read-only or updateable at the cursor or field level.
• As forward-only or fully scrollable.
• With the cursor keyset located on the server.
• Sensitive to underlying table changes caused by other applications (such as membership, sort, inserts, updates, and deletes).
• Existing on either the server or the client.

Read-only cursors help users browse through the result set, and read/write cursors can implement individual row updates. Complex cursors can be defined with keysets that point back to base table rows. While some cursors are read-only in a forward direction, others can move back and forth and provide a dynamic refresh of the result set based on changes that other applications are making to the database.

Not all applications need to use cursors to access or update data. Some queries simply do not require direct row updating by using a cursor. Cursors should be one of the last techniques you choose to retrieve data—and then you should choose the lowest impact cursor possible. When you create a result set by using a stored procedure, the result set is not updateable using cursor edit or update methods.

Concurrency

In some multi-user applications it is vitally important for the data presented to the end user to be as current as possible. A classic example of such a system is an airline reservation system, where many users might be contending for the same seat on a given flight (and thus, a single record). In a case like this, the application design must handle concurrent operations on a single record.

In other applications, concurrency is not as important. In such cases, the expense involved in keeping the data current at all times cannot be justified.

Position

A cursor also keeps track of the current position in a result set. Think of the cursor position as a pointer to the current record, similar to the way an array index points to the value at that particular location in the array.

Scrollability

The type of cursor employed by your application also affects the ability to move forward and backward through the rows in a result set; this is sometimes called scrollability. The ability to move forward and backward through a result set adds to the complexity of the cursor, and is therefore more expensive to implement. For this reason, you should ask for a cursor with this functionality only when necessary.

Types of Cursors

As a general rule, your application should use the simplest cursor that provides the required data access. Each additional cursor characteristic beyond the basics (forward-only, read-only, static, scrolling, unbuffered) has a price—in client memory, network load, or performance. In many cases, the default cursor options generate a more complex cursor than your application actually needs.

Your choice of cursor type depends on how your application uses the result set and also on several design considerations, including the size of the result set, the percentage of the data likely to be used, sensitivity to data changes, and application performance requirements.

At its most basic, your cursor choice depends on whether you need to change or simply view the data:

• If you just need to scroll through a set of results, but not change data, use a forward-only or static cursor.
• If you have a large result set and need to select just a few rows, use a keyset cursor.
• If you want to synchronize a result set with recent adds, changes, and deletes by all concurrent users, use a dynamic cursor.

Although each cursor type seems to be distinct, keep in mind that these cursor types are not so much different varieties as simply the result of overlapping characteristics and options.

This section contains the following topics.

• Forward-Only Cursors
• Static Cursors
• Keyset Cursors
• Dynamic Cursors

The Significance of Cursor Location

Every cursor uses temporary resources to hold its data. These resources can be memory, a disk paging file, temporary disk files, or even temporary storage in the database. The cursor is called a client-side cursor when these resources are located on the client computer. The cursor is called a server-side cursor when these resources are located on the server.

Client-Side Cursors

In ADO, call for a client-side cursor by using the adUseClient CursorLocationEnum. With a non-keyset client-side cursor, the server sends the entire result set across the network to the client computer. The client computer provides and manages the temporary resources needed by the cursor and result set. The client-side application can browse through the entire result set to determine which rows it requires.

Static and keyset-driven client-side cursors may place a significant load on your workstation if they include too many rows. While all of the cursor libraries are capable of building cursors with thousands of rows, applications designed to fetch such large rowsets may perform poorly. There are exceptions, of course. For some applications, a large client-side cursor might be perfectly appropriate and performance might not be an issue.

One obvious benefit of the client-side cursor is quick response. After the result set has been downloaded to the client computer, browsing through the rows is very fast. Your application is generally more scalable with client-side cursors because the cursor's resource requirements are placed on each separate client and not on the server.

Server-Side Cursors

In ADO, call for a server-side cursor by using the adUseServer CursorLocationEnum. With a server-side cursor, the server manages the result set using resources provided by the server computer. The server-side cursor returns only the requested data over the network. This type of cursor can sometimes provide better performance than the client-side cursor, especially in situations where excessive network traffic is a problem.

However, it is important to point out that a server-side cursor is—at least temporarily—consuming precious server resources for every active client. You must plan accordingly to ensure that your server hardware is capable of managing all of the server-side cursors requested by active clients. Also, a server-side cursor can be slow because it provides only single row access—there is no batch cursor available.

Server-side cursors are useful when inserting, updating, or deleting records. With server-side cursors, you can have multiple active statements on the same connection.

What is a Lock?

Locking is the process by which a DBMS restricts access to a row in a multi-user environment. When a row or column is exclusively locked, other users are not permitted to access the locked data until the lock is released. This ensures that two users cannot simultaneously update the same column in a row.

Locks can be very expensive from a resource perspective and should be used only when required to preserve data integrity. In a database where hundreds or thousands of users could be trying to access a record every second—such as a database connected to the Internet—unnecessary locking could quickly result in slower performance in your application.

You can control how the data source and the ADO cursor library manage concurrency by choosing the appropriate locking option.

Set the LockType property before opening a Recordset to specify what type of locking the provider should use when opening it. Read the property to return the type of locking in use on an open Recordset object.

Providers might not support all lock types. If a provider cannot support the requested LockType setting, it will substitute another type of locking. To determine the actual locking functionality available in a Recordset object, use the Supports method with adUpdate and adUpdateBatch.

The adLockPessimistic setting is not supported if the CursorLocation property is set to adUseClient. If an unsupported value is set, no error will result; the closest supported LockType will be used instead.

The LockType property is read/write when the Recordset is closed, and read-only when it is open.

Types of Locks

adLockBatchOptimistic

Indicates optimistic batch updates. Required for batch update mode.

Many applications fetch a number of rows at once and then need to make coordinated updates that include the entire set of rows to be inserted, updated, or deleted. With batch cursors, only one round trip to the server is needed, thus improving update performance and decreasing network traffic. Using a batch cursor library, you can create a static cursor and then disconnect from the data source. At this point you can make changes to the rows and subsequently reconnect and post the changes to the data source in a batch.

adLockOptimistic

Indicates that the provider uses optimistic locking—locking records only when you call the Update method. This means that there is a chance that another user may change the data between the time you edit the record and when you call Update, which creates conflicts. Use this lock type in situations where the chances of a collision are low or where collisions can be readily resolved.

adLockPessimistic

Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately before editing. Of course, this means that the records are unavailable to other users once you begin to edit, until you release the lock by calling Update. Use this type of lock in a system where you cannot afford to have concurrent changes to data, such as in a reservation system.

adLockReadOnly

Indicates read-only records. You cannot alter the data. A read-only lock is the "fastest" type of lock because it does not require the server to maintain a lock on the records.

adLockUnspecified

Does not specify a type of lock.

Cursor and Lock Characteristics

While the characteristics of a cursor depend upon capabilities of the provider, the following advantages and disadvantages generally apply to the various types of cursors and locks.

Cursor or lock type	Advantages	Disadvantages
adOpenForwardOnly	Low resource requirements	Cannot scroll backward No data concurrency
adOpenStatic	Scrollable	No data concurrency
adOpenKeyset	Some data concurrency Scrollable	Higher resource requirements Not available in disconnected scenario
adOpenDynamic	High data concurrency Scrollable	Highest resource requirements Not available in disconnected scenario
adLockReadOnly	Low resource requirements Highly scalable	Data not updatable through cursor
adLockBatchOptimistic	Batch updates Allows disconnected scenarios Other users able to access data	Data can be changed by multiple users at once
adLockPessimistic	Data cannot be changed by other users while locked	Prevents other users from accessing data while locked
adLockOptimistic	Other users able to access data	Data can be changed by multiple users at once