www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

SQL Reference

Datatypes
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
ISOLATION LOCK_ESCALATION_PCT PARAM_BATCH
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices

8.24. SET Statement

8.24.1. ISOLATION

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.

Setting the isolation level
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.


8.24.2. LOCK_ESCALATION_PCT

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.


8.24.3. PARAM_BATCH

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.