|
|
|
The set session statement cannot be issued within a transaction. The set session statement must be issued before a transaction commences and the settings apply until that session is completed.
Ingres supports these levels of isolation as defined by the ANSI/ISO SQL92 standard:
Isolation Level |
Description |
|---|---|
READ UNCOMMITTED |
This level is equivalent to what is commonly known as readlock=nolock. Isolation level must be specified for an entire transaction. |
READ COMMITED |
This isolation level always sees committed data because data is locked before being read. The lock remains in effect until the cursor is positioned to the next page/row or closed. However, if any data on the current item of the cursor is changed, the lock is held until the transaction commits. When acquiring a lock on a page/row, Ingres releases the previous page/row if the previous page/row was not updated. |
REPEATABLE READ |
Data is always locked before being read. Locks are released only on nonqualifying data. All other locks are held until the end of the transaction. |
SERIALIZABLE |
Locks are required on all data before being read. No locks are released until the transaction ends. |
SET SESSION AUTHORIZATION <username> |
Allows a privileged user to alter their effective user within a session. Requires security or dbadmin privileges. Effective user immediately becomes the stipulated username. |
REAREAD WRITE is specified, the level of isolation is READ_COMMITTED, REPEATABLE_READ, or SERIALIZABLE.
Note: This is an ANSI rule, which Ingres does not enforce.
If session access mode is not specified and level of isolation is READ_UNCOMMITTED, READ ONLY is implicit. Otherwise, READ WRITE is implicit.
When a session access mode of READ ONLY is in effect, database modification (insert, update, delete, load, and DDL operations) is disallowed, and an SQLSTATE of 25000 (invalid session state) is returned. Temporary tables are immune to this test and are always writable.
When a READ_ONLY session is begun, it registers itself with the logging system and is allowed to proceed even when a ckpdb is pending against the session's database.
Note: The access mode of a session has no effect on the locking mode of the session.
|
|
|
The set transaction statement cannot be issued within a transaction. The set transaction statement must be issued before a transaction commences and the settings last only until that transaction is completed.
Ingres supports these levels of isolation as defined by the ANSI/ISO SQL92 standard:
Isolation Level |
Description |
|---|---|
READ UNCOMMITTED |
This level is equivalent to what is commonly known as readlock=nolock. Isolation level must be specified for an entire transaction. |
READ COMMITTED |
This isolation level always sees committed data because data is locked before being read. The lock remains in effect until the cursor is positioned to the next page/row or closed. However, if any data on the current item of the cursor is changed, the lock is held until the transaction commits. When acquiring a lock on a page/row, Ingres releases the previous page/row if the previous page/row was not updated. |
REPEATABLE READ |
With this isolation level, data is always locked before being read. Locks are released only on nonqualifying data. All other locks are held until the end of the transaction. |
SERIALIZABLE |
Locks are required on all data before being read. No locks are released until the transaction ends. |
If READ WRITE is specified, the level of isolation is READ_COMMITTED, REPEATABLE_READ, or SERIALIZABLE.
Note: This is an ANSI rule, which Ingres does not enforce.
If transaction access mode is not specified and level of isolation is READ_UNCOMMITTED, READ ONLY is implicit. Otherwise, READ WRITE is implicit.
When a transaction access mode of READ ONLY is in effect, database modification (insert, update, delete, load, and DDL operations) is disallowed, and an SQLSTATE of 25000 (invalid transaction state) is returned. Temporary tables are immune to this test and are always writable.
When a READ_ONLY transaction is begun, it registers itself with the logging system and is allowed to proceed even when a ckpdb is pending against the transaction's database.
Note that the access mode of a transaction has no effect on the locking mode of the transaction.
|
|
|
When a Unicode value is being coerced into local character set which has no equivalent character for the Unicode value an error explaining that such coercion is not possible, is returned by default.
If unicode_substitution is set for the session then instead of returning the error such unmapped code points will be replaced with a designated substitution character as assigned in the mapping table for the character set. The substitution character is used from the default subchar value specified in the mapping file. To override this value provide a character value for substitution character with this statement.
Set nounicode_substitution in the session will revert to the default behavior.
|
|
|