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 KEYSET-DRIVEN (or 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:
- Specify
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 COMMIT/ROLLBACK. - Specify
FOR UPDATE BY TIMESTAMP or 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.
Table hints
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 )
The 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 NOLOCK.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.READPAST. The READPAST table hint is supported for SELECT statements (only) in conjunction with isolation level 1. READPAST avoids 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, READPAST exhibits unsafe semantics by simply eliminating uncommitted updates from the computation. However, the significant advantage to READPAST is 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.
OPTION clause
Beginning with SQL Anywhere 10.0.1, data manipulation (DML) statements (SELECT, 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 OPTIONclause:
ISOLATION_LEVELoptionMAX_QUERY_TASKSoptionOPTIMIZATION_GOALoptionOPTIMIZATION_LEVELoptionOPTIMIZATION_WORKLOADoptionUSER_ESTIMATES option
We will be looking at expanding this list to include other concurrency-control-related options in a future SQL Anywhere release.
