The world's most popular open source database
Mike Hillyer is a Technical Writer for MySQL AB and lives in Alberta, Canada.
By Mike Hillyer
One of the most common sources of trouble for the beginning VB / MySQL developer seems to revolve around which cursor location to use. Related to the cursor location problem is the choice of cursor type and lock type. The purpose of this article is to assist the Visual Basic developer in choosing the proper cursor location, cursor type, and lock type to use when programming Visual Basic (or VB) applications that use MySQL as a back-end database through ADO and Connector/ODBC (MyODBC).
This article will assume that the reader is using the MySQL RDBMS, but should apply to developers using other database management systems. For an overview of why MySQL is a good choice for Visual Basic developers, see the Why VB/MySQL article on this site. This sample requires the latest version of Connector/ODBC (MyODBC), available for download here (currently 3.51.06).
On the Visual Basic side, I recommend you have service pack 5 installed for VB, which you can download here. Additionally, the latest version of MDAC (Microsoft Data Access Components) should be installed, and can be found here (currently 2.7 SP1). Finally, this article applies to ADO 2.7 and VB6. It is not applicable to ADO.NET under VB.NET (Unless you reference ADODB in your project and use it for database access).
In ADO, when we talk about cursors, we are essentially talking about a set
of rows. When you execute a query that returns rows of data, such as
SELECT * FROM mytable, the resulting data is handled using a
cursor. A cursor can be located either on the client with the
adUseClient argument, or on the server with the
adUseServer argument. In addition, the are 4 types of cursor:
The different types and locations will be discussed in further detail below. Your choice of cursor type and cursor location will affect what you are able to do with the data you retrieve, and how changes made to the data by other users are reflected in your copy of the data.
The ADODB.Connection object (the ADO object used to broker all data
exchanges between the VB application and the MySQL server) has a property known
CursorLocation which is used to set/retrieve the cursor
location that will be used by any recordset objects that access their data
through the connection object.
CursorLocation property can only be set while the
connection is closed, and the property will be inherited by any recordset
objects that access their data through the given connection object. Recordset
objects can also explicitly set a cursorlocation different than the connection
objects cursorlocation as long as it is set before the recordset is open. The
two options available for this property are
adUseServer being the default
When using the adUseServer server-side cursorlocation, responsibility for handling the data generated by a query lies with the database server. MySQL itself does not support server-side cursors, so the data handling is actually done by the Connector / ODBC driver. The benefit of server-side cursors is that we gain access to the dynamic cursor type. This allows us to see any changes to the data that are made by other users in the data our application is accessing.
For example: let's say we are selling tickets to a concert with our application, we need to know that a given seat is available for sale in real-time to ensure we do not double-book the seat. With a server-side cursor, we can be sure that the data we are manipulating is the most current possible. In addition, we have the ability to lock the data we are working on as we edit it, to make sure our changes are going to be posted to the database successfully.
With a server-side cursor (adUseServer), we have access to the
adOpenForwardOnly cursor types, and
all four of the recordset lock types, which will be discussed below.
It should be noted that using a server-side cursor, and the adOpenDynamic cursor in particular, will result in a significant performance loss, and should be avoided if at all possible. In addition, certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor.
Client-side cursors, specified with the adUseClient keyword, are handled internally by ADO. These cursors offer more functionality than their server-side counterparts, and also result in less load being placed on the server. Most advanced ADO functionality is designed for use with client-side cursors, and I personally use client-side cursors for all my applications (with one exception).
When using a client-side
adUseClient cursor, only the
adOpenStatic cursor is available, and we cannot use the
adLockPessimistic lock type (see below).
Client-side cursors also help decrease load on our MySQL server, since with a static cursor data is sent to the client and then the server has no further communications with the client. This allows your server to scale a lot better than with server-side cursors.
In addition to the two cursor locations, there are four cursor types, three of which are supported under Connector/ODBC:
The different cursor types support different functionality and features, and
I will now discuss each one in detail. The fourth cursor type,
adOpenKeySet, is not currently supported by MySQL / MyODBC.
The static cursor is the only cursor type that is currently available when
adUseClient as your cursor location. With a static cursor, the
server will send the result set to the client, after which there will be no
further communication from the server to the client. The client may communicate
with the server to send changes back to the server. This makes the static
cursor more resource-intensive for the client and less resource-intensive for
the server, as the result set is stored in the client's memory instead of the
If a different client makes changes to the underlying data after the query results are sent, the original client will receive no notification of the change. A static cursor is bi-directional, meaning that your application can move forwards and backwards through the recordset. The following methods are available to a recordset using a static cursor and the adLockOptimistic lock type (more on lock types later):
The static cursor will also show an accurate value for the RecordCount
property of your recordset, and supports the getchunk and appendchunk methods
for dealing with BLOB data. If you are having trouble with either of these
problems, explicitly setting your connection's cursorlocation to
adUseClient should solve them.
One handy feature of the static cursor is the ability to fetch data
asynchronously. When data is fetched asynchronously., a separate thread is
started to handle row retrieval, and your VB application can begin processing
returned rows immediately. An in depth article on asynchronous data fetching
is pending, but to activate this feature, simple use the
adFetchAsync option during your
If you specify any cursor type other than
opening a recordset with an
adUseClient cursor location, it will
be automatically converted to a static cursor.
adForwardOnly cursor type is the fastest performing
cursortype, and also the most limited. The forward-only cursor does not support
the RecordCount property, and does not support the MovePrevious methods of the
The most efficient way to access data for display to the screen out output
to a file is to use a
adOpenForwardOnly cursor with a
adLockReadOnly lock type when opening a recordset. This
combination is often referred to as a Firehose Cursor. A firehose cursor
bypasses a lot of handling code between the client and server and allows for
very fast data access when moving sequentially through the resulting rows.
The following recordset methods are supported when using a forward-only cursor with an optimistic lock:
In addition, the forward-only cursor type supports non-caching queries. While an asynchronous query allows data to be worked on immediately, it offers no memory benefits when accessing large resultsets, as all rows eventually wind up in memory, taxing system resources when accessing a large number of rows, or a medium number of rows when BLOB data is involved.
With MySQL and Connector/ODBC, we can specify option 1048576 in our
connection string or check off the option "Don't Cache Results" in the ODBC
manager in order to specify to the ODBC driver that it should only retrieve one
row at a time from the server. With this option set, memory usage on the client
is limited as only one row at a time is stored in memory. With every call to
MoveNext method, the previous row is discarded and
the next row is queried from the server.
While the forward-only cursor is the most efficient of the cursor types, the
dynamic cursor, specified but
adOpenDynamic, is the least
efficient. Because of it's inefficiency, dynamic cursor support must be
manually activated by using option 32 in your connection string, or by checking
"Enable Dynamic Cursor" in the ODBC manager. Without this option enabled, any
cursortype other than forward-only with be automatically converted to a static
cursor, with it enabled, all cursor types other than forward-only will be
converted to dynamic.
Why is a dynamic cursor so slow? As there is no native support for dynamic, server-side cursors in MySQL, every call to a row-moving method(MoveNext, MovePrevious, etc.) results in the Connector/ODBC driver converting your method call to a SQL query, posting the query, and returning the resulting row. This also means that for a dynamic cursor to work properly, your underlying table needs a primary key column to determine the next/previous row with. As such, dynamic cursors are not recommended unless absolutely necessary.
The dynamic cursor supports the following recordset methods when opened with a optimistic lock:
While Dynamic cursors can be beneficial for multi-user applications, it is
best to avoid them when possible, and work around multi-user issues when
possible by calling the
when possible, and executing UPDATE queries that increment and decrement count
values instead of using the recordset to do updates (i.e. rather than getting
an inventory count in a recordset, incrementing it in VB, and doing a
recordset.update, use the connection object to execute a query similar to
UPDATE inventory SET count = count - 1 WHERE itemcode = 5)
While cursor locations and cursor types specify how our data is going to be
handled, the lock type property specifies how we are going to lock the
underlying data to protect any changes we make and ensure they are processed.
There are four different lock types, and the locktype is set in the recordset
object as part of the open method (it can also be set using the LockType
property of the recordset object). The four locktypes are:
four locktypes are available to a server-side cursor, the
adLockPessimistic locktype is unavailable to a client-side
The default lock type is
adLockReadOnly. A read-only lock is the
most efficient when accessing data, as there is no checking for data changes
and therefore no extra traffic between the client and server while loading
As the name implies, using a read-only lock will block you from making any
changes to the table. If you find yourself with an error message like "Current
recordset does not support updating", then you need to change away from
adLockReadOnly lock type.
An optimistic lock is used for modifications that either happen in a low-concurrency environment, or where having multiple users making changes to the same records is not a major concern. With an optimistic lock, the table or row locks will occur when the update method of the recordset object is called. This will ensure the change is successfully made, but will not prevent other users from changing the underlying data while you are modifying it in VB.
adLockOptimistic lock type is typically your best choice
when deciding on a table lock for a non-read-only situation. In almost all my
applications, the only two lock types I use are
When using the adBatchOptimistic lock type, your changes will be cached
locally until the recordset's UpdateBatch method is called. When UpdateBatch is
called, all changes will be pushed to the server in a group. This can make the
bulk insert of a large number of records more efficient. (Note: Calling
ALTER TABLE mytable DISABLE KEYS before a large batch of inserts,
ALTER TABLE mytable ENABLE KEYS after the batch
completes, can dramatically speed up the batch insert process, as MySQL can
rebuild an index faster than it can add one entry at a time).
In a situation of high concurrency, with multiple users modifying the same
data, you may need a pessimistic lock type. With
asLockPessimistic, the underlying rows (or table) will be locked
as soon as you begin making changes to the current record, and will not be
unlocked until the Update method is called.
While this will ensure that you do not have overlapping changed with other users, it could cause performance issues, especially with a MyISAM table, with features table-level locking only. Make sure that the changes are immediately followed by the recordset's update method, and that there is no break for user input between a change and the update in order to ensure no long breaks (and potentially canceled locks by the database) in the operation of the database.
adLockPessimistic has it's place, the same advice I gave
regarding dynamic cursors applies: avoid it when possible, as it is very
resource intensive and involves a lot more work both on the client and server
While there are a large number of potential CursorType/CursorLocation combinations, the ones that are currently available to the MySQL/VB developer are: adUseClient/adOpenStatic, adUseServer/adOpenForwardOnly, and adUseServer/adOpenDynamic.
For most uses, adUseClient/adOpenStatic is your best choice, with adLockReadOnly as your lock type for any read-only operations (export to a file, load rows to a listview, combobox, etc.) and adLockOptimistic as your lock type for any read/write operations.
adOpenDynamic and adLockPessimistic are best suited for high-concurrency situations where you need to ensure that multiple users do not corrupt each other's data. While these offer the most current views of data and the most restrictive locking, they do so at a severe price as far as performance is concerned.
The combination of adUseServer/adOpenForwardOnly/adLockReadonly offers the best performance overall for operations like populating controls and exporting to files. When combined with option 1048576 (Don't cache query results), adOpenForwardOnly also provides excellent memory efficiency, as only one record at a time is loaded into memory. Be awate that if a locktype other than adLockReadOnly is used, memory usage will slowly build as rows are loaded into memory and kept in case they are needed for an update or cancel statement. The one row at a time operation is only present with an adLockReadOnly/adOpenForwardOnly combination.