The way your program handles its cursors can have a major impact on database performance. Recall from Chapter 12 that the SQL transaction model guarantees the consistency of data during a transaction. In cursor terms, this means that your program can declare a cursor, open it, fetch the query results, close it, reopen it, and fetch the query results again—and be guaranteed that the query results will be identical both times. The program can also fetch the same row through two different cursors and be guaranteed that the results will be identical. In fact, the data is guaranteed to remain consistent until your program issues a COMMIT or ROLLBACK to end the transaction. Because the consistency is not guaranteed across transactions, both the COMMIT and ROLLBACK statements automatically close all open cursors.
Behind the scenes, the DBMS provides this consistency guarantee by locking all of the rows of query results, preventing other users from modifying them. If the query produces many rows of data, a major portion of a table may be locked by the cursor. Furthermore, if your program waits for user input after fetching each row (for example, to let the user verify data displayed on the screen), parts of the database may be locked for a very long time. In an extreme case, the user might leave for lunch in mid-transaction, locking out other users for an hour or more!
To minimize the amount of locking required, you should follow these guidelines when writing interactive query programs:
- Keep transactions as short as possible.
- Issue a COMMIT statement immediately after every query and as soon as possible after your program has completed an update.
- Avoid programs that require a great deal of user interaction or that browse through many rows of data.
- If you know that the program will not try to refetch a row of data after the cursor has moved past it, use one of the less restrictive isolation modes described in Chapter 12. This allows the DBMS to unlock a row as soon as the next FETCH statement is issued.
- Avoid the use of scroll cursors unless you have taken other actions to eliminate or minimize the extra database locking they will cause.
- Explicitly specify a READ ONLY cursor, if possible.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.