Vantage assigns locking levels and severities to SQL requests by default.
When necessary, Vantage upgrades locks while processing system- or user-generated transactions. The most frequent upgrade is from a READ lock to a WRITE lock. This occurs whenever you select a row and then make an update request for the same row before Vantage commits the transaction.
Default Lock Assignments
The following table lists default lock assignments for SQL requests and their access strategies.
| Access Type NUSI or FTS |
|---|
| Database |
| Table |
| Table |
| Not applicable |
|
| Table |
| Row hash |
| Table |
Changing Lock Assignments Using the LOCKING Request Modifier
Depending on the assigned lock and the individual SQL request, you may be able to change default lock assignments using the LOCKING request modifier. You can upgrade any lower severity lock to a higher severity lock, but the only downgrade permitted is from a READ lock to an ACCESS lock.
The following table summarizes the allowable changes from default database assignment lock to user-specified database lock.
| Default Assignment Lock | User-specified Lock | Change |
|---|---|---|
| ACCESS Even though a CHECKSUM lock is essentially identical to an ACCESS lock, you can only specify CHECKSUM locks explicitly using the LOCKING request modifier. You cannot upgrade CHECKSUM locks to a higher severity lock. This is because Vantage never specifies CHECKSUM as a default lock severity. |
ACCESS | Valid but redundant |
| READ | READ | |
| WRITE | WRITE | |
| EXCLUSIVE | EXCLUSIVE | |
| ACCESS | READ | Valid upgrade |
| WRITE | ||
| EXCLUSIVE | ||
| READ | WRITE | |
| EXCLUSIVE | ||
| WRITE | EXCLUSIVE | |
| READ | ACCESS | Valid downgrade |
The following table combines the information from the two previous tables to indicate associations between individual SQL DML statements and lock upgrades and downgrades at the rowhash, view, and table database object levels:
| LOCKING Request Modifier Severity Specification | Applicable SQL DML Statements |
|---|---|
| EXCLUSIVE |
|
| WRITE |
|
| READ | SELECT |
| ACCESS | SELECT |
The reason you can only specify the LOCKING FOR EXCLUSIVE modifier for DELETE, INSERT, MERGE, and UPDATE requests is that the default lock severity for these statements is WRITE. You cannot downgrade a WRITE lock because doing so compromises the integrity of the database. Because the SELECT statement does not update data, and therefore its actions cannot compromise database integrity, you are permitted to change its default locking severity to any other severity. This option does not extend to its SELECT AND CONSUME variant, for which the severity can only be upgraded to WRITE or EXCLUSIVE.
Rules for Upgrading Locks
- If two transactions concurrently hold READ locks for the same data and the first transaction enters an update request, then its READ lock cannot be upgraded to a WRITE lock until the READ lock for the second transaction is released.
- If other transactions are awaiting locks for the same data when the first transaction enters its update request, its READ lock is upgraded before the waiting transactions are given locks. Thus, upgrading an existing lock has higher priority than does granting a new lock.
Guidelines for Changing Default Lock Assignments and Changing Intratransaction Request Orders to Maximize Concurrency
- Use ACCESS locks in place of READ locks whenever an application can tolerate dirty reads.
- A SELECT request that requires a READ lock on a table cannot run concurrently with an running CREATE INDEX or ALTER TABLE ... FALLBACK request for the same table.
Instead, specify a READ lock for the CREATE INDEX or ALTER TABLE request to permit concurrency (see LOCKING Request Modifier.
- If the CREATE INDEX or ALTER TABLE ... FALLBACK LOCKING request modifier specifies WRITE (or if there is no LOCKING request modifier specified), specify an ACCESS lock in a LOCKING request modifier on your SELECT request to permit concurrency.
The ALTER TABLE operation can be to add FALLBACK only; if other table attributes are added, then ALTER TABLE cannot run concurrently with SELECT.
- Because requests for WRITE locks can cause blocked transactions and deadlocks, consider running only read-only transactions (or access-only if a LOCKING FOR WRITE clause is specified) concurrently with ALTER TABLE ... FALLBACK or CREATE INDEX statements.
- When running long transactions concurrently with CREATE INDEX or ALTER TABLE ... FALLBACK, the CREATE INDEX or ALTER TABLE request may not complete until the long running transactions have completed.
- To avoid the chance of deadlocks with other DML transactions or DDL requests, consider writing your DML transactions to immediately get the highest severity lock required rather than try to upgrade a less severe lock at a later time during transaction processing.
- Put SELECT AND CONSUME requests as early as possible in a transaction to avoid conflicts with other database resources. This minimizes the likelihood that a SELECT AND CONSUME TOP 1 request enters a delayed state while holding locks on resources that may be needed by other requests.