From the Archives: Customizing SQL Requests With Hints
In this post, originally written by Glenn Paulley and posted to sybase.com in June of 2009, Glenn talks about customizing query semantics via the use of hints. While hints can be incredibly useful in some situations, it is important to note that the vast majority of the time, SQL Anywhere query optimization and execution does an incredible job of doing ‘the right thing’, taking into account all of the context in which it is running in order to provide performant execution of queries and DML.
There are a number of mechanisms one can use to affect the precise semantics of an SQL query, in particular to insulate (or expose) the affects of concurrent transactions to the results of a particular SQL statement. One such mechanism is the type of cursor that is used. As an example,
INSENSITIVE cursors materialize the entire result set of a SQL query at
OPEN time, insulating the query from the effects on concurrent updates, even from the same transaction, prior to the first
FETCH of that result. On the other hand, SQL Anywhere
SCROLL) cursors memoize rows as they are FETCHed by the application, and return a warning (error) back to the application if the same result row is re-FETCHED and has been concurrently updated (deleted).
Another mechanism is the isolation level used by the SQL statement. Lower ANSI SQL isolation levels than SERIALIZABLE have obvious benefits to improving concurrency, but at the risk of incurring anomalies during query execution because of concurrent updates, and interactions between them. Among other semantic effects, the use of table hints permit one to specify semantic changes on a query, or even table, basis. Ordinarily I’m not in favour of the use of hints in SQL queries, as I have a strong bias to letting the query optimizer choose the access plan as it sees fit. However, in many instances query or table hints can be extremely useful, particularly enabling fine-grained control of locking behaviour.
SELECT FOR UPDATE
Before we get to table hints, I’d like to mention two concurrency control hints at the statement level that are specified with the
FOR UPDATE syntax. The basic
FOR UPDATEclause explicitly declares an updateable cursor; however, at isolation levels 0 and 1 long-term locks on these are not acquired, and hence these rows are open to modification or deletion by other connections. To verify the subsequent updateability of these rows, there are two options:
FOR UPDATE BY LOCK. This causes the acquisition of an INTENT row lock on each row as it is
FETCHed by the application. INTENT locks permit other connections to read the row, but no other connection can acquire an INTENT or WRITE lock on it. INTENT locks are long-term locks that are held until
FOR UPDATE BY TIMESTAMPor
FOR UPDATE BY VALUES. In this case, SQL Anywhere forces the use of a KEYSET-DRIVEN cursor, as a form of optimistic concurrency control, to enable notification that a particular row has been altered or deleted by another connection.
As with other systems, such as Microsoft SQL Server, SQL Anywhere supports table hints using an additional
WITH clause. Here is an example, using the demo.db sample database:
SELECT * FROM CUSTOMERS WITH ( NOLOCK )
NOLOCKtable hint causes the server to access the Customers table at isolation level 0. Note that table hints only apply to base or global shared temporary tables; hints are ignored if they are used with a view or proxy table. Here is a complete list of the table hints supported with by a SQL Anywhere 11.0.1 server:
NOLOCK– use isolation level 0 (no READ locking). Compatible with Microsoft SQL Server.
READUNCOMMITTED– synonym for
READCOMMITTED– use short-term read locks at isolation level 1.
REPEATABLEREAD– use read locks at isolation level 2.
SERIALIZABLE– use read locks at isolation level 3.
HOLDLOCK– synonym for
SERIALIZABLE, also supported by Sybase Adaptive Server Enterprise and Microsoft SQL Server.
READPASTtable hint is supported for
SELECTstatements (only) in conjunction with isolation level 1.
READPASTavoids blocking during a scan – either an index scan or a table scan – by simply “jumping” over rows that are locked with INTENT or WRITE locks. In this sense,
READPASTexhibits unsafe semantics by simply eliminating uncommitted updates from the computation. However, the significant advantage to
READPASTis that it is extremely useful for maintaining queues, or key pools, within base tables, yet avoiding concurrency conditions due to blocking.
READPASTis also supported by Microsoft SQL Server and Sybase Adaptive Server Enterprise.
UPDLOCK– apply INTENT locks to each row of the scan.
XLOCK– apply WRITE locks to each row of the scan, prohibiting any other connections from accessing the rows except for transactions at isolation level 0.
Finally, the table hint
FASTFIRSTROW causes the SQL Anywhere optimizer to use an optimization goal of
FIRST-ROW for the
SELECT block containing that table reference. Note that it doesn’t matter which table receives the hint; any table reference in a
SELECT block accompanied by a
FASTFIRSTROWhint changes the goal.
Beginning with SQL Anywhere 10.0.1, data manipulation (DML) statements (
UPDATE, etc) support an
OPTION clause. A useful ability of the OPTION clause is to permit the application developer to override optimization bypass and force cost-based optimization of the statement. In addition, one can set specific connection options, such as
ISOLATION LEVEL or
OPTIMIZATION_GOAL, for this statement alone, avoiding the need to alter these option settings individually with a
SET OPTION statement. As an example: SELECT * FROM Customers OPTION (OPTIMIZATION_LEVEL = 2, ISOLATION_LEVEL = 2 ) At present, the following connection options can be set in a query’s
We will be looking at expanding this list to include other concurrency-control-related options in a future SQL Anywhere release.