This allows setting a transaction isolation in a stored procedure or trigger body. The values are:
These are case insensitive strings.
This takes effect dynamically until replaced by another SET ISOLATION setting. The effect never persists over the return of the containing procedure or trigger. The effect does extend into procedures or triggers called from after executing the SET ISOLATION statement.
set isolation='serializable';
The initial isolation comes from the SQL_TXN_ISOLATION statement option in the ODBC API (SQLSetConnectOption). The default isolation is repeatable read.
This controls the escalation from row locking to page locking. A set of row locks can be converted into one page lock if: (a) All the row locks on the page belong to the same transaction, or, (b) No other transaction waits for any of these locks. The value of this parameter is the percentage of rows on a page that must be held by the transaction before the locking goes to page level. The default is 50, meaning that for a page of 120 rows the 61st row lock will escalate the lock if all the previous locks belong to the same transaction and there is no wait pending on any. A value of -1 means that locking is always at page level if there is more than one lock on the page. A value in excess of 100 causes lock escalation to be turned off. The effect of this setting is global and persists until the server is restarted. This setting does not affect the semantic of locking.
This sets the batch size used by the virtual database array parameter optimization batch size. This causes several consecutive executes of the same statement to be grouped as a single ODBC operation with array parameters. This optimizes joins of tables on different servers and searched updates, inserts and deletes on attached tables. Most ODBC drivers do not support array parameters. A value of 1 or 0 disables the optimization. This should be done if there is a driver which falsely claims to support array parameters. If a given driver returns an error when setting array parameters the VDB will detect this and will not try to use them.
The effect of this setting is global and persists until the server is restarted. The default value comes from the ArrayParameters configuration parameter.
Previous
GRANT, REVOKE Statement |
Chapter Contents |
Next
Best Effort Union |