Concurrency Control

With increased transaction isolation usually comes reduced concurrency, or the ability of two transactions to use the same data at the same time. The reason for this is that transaction isolation is usually implemented by locking rows and, as more rows are locked, fewer transactions can be completed without being blocked at least temporarily by a locked row. While reduced concurrency is generally accepted as a trade-off for the higher transaction isolation levels necessary to maintain database integrity, it can become a problem in interactive applications with high read/write activity that use cursors.

For example, suppose an application executes the SQL statement SELECT * FROM Orders. It calls SQLFetchScroll to scroll around the result set and allows the user to update, delete, or insert orders. After the user updates, deletes, or inserts an order, the application commits the transaction.

If the isolation level is Repeatable Read, the transaction might—depending on how it is implemented—lock each row returned by SQLFetchScroll. If the isolation level is Serializable, the transaction might lock the entire Orders table. In either case, the transaction releases its locks only when it is committed or rolled back. Thus, if the user spends a lot of time reading orders and very little time updating, deleting, or inserting them, the transaction could easily lock a large number of rows, making them unavailable to other users.

Note that this is a problem even if the cursor is read-only and the application only allows the user to read existing orders. In this case, the application commits the transaction—and releases locks—when it calls SQLCloseCursor (in auto-commit mode) or SQLEndTran (in manual-commit mode).