Overview
This document describes an error that occurs while performing a change transaction in the memory table.
Version
Altibase version 4.3.9 or later
Symptom
This error may occur while performing a change transaction on the memory table.
iSQL>
update
t5
set
c1=
'xxxxxxxxx'
;
[ERR-11118 : The
update
log
size
'10486749'
is
bigger than TRX_UPDATE_MAX_LOGSIZE
'10485760'
]
iSQL>
The error code differs depending on the Altibase version.
Cause
This error occurs because the size of the online transaction log created by one change transaction exceeds the TRX_UPDATE_MAX_LOGSIZE setting value.
TRX_UPDATE_MAX_LOGSIZE is an Altibase server property and serves to prevent excessive use of memory due to versioning of the memory table created by a large number of change transactions.
Versioning of the memory table?
Altibase uses the Multi-Version Concurrency Control (MVCC) technique for concurrency control. MVCC refers to creating a new version of the record by executing the DML statement on a copy of the record, leaving the record as it was when a DML statement occurs for a record. With this technique, lookup transactions for one record are not affected by change transactions.
Due to the characteristics of each disk and memory, the disk table is in-place and the memory table is out-place and different MVCC techniques are used.
Out-place MVCC of the memory table keeps multiple versions by adding new versions to the new space, leaving the existing records intact when an update occurs on one record. TRX_UPDATE_MAX_LOGSIZE and LOCK_ESCALATION_MEMORY_SIZE properties are provided to prevent this from incurring load on the system, such as increased memory.
Since there is no previous version of INSERT, versioning does not occur, and since there is no new version of DELETE, only the delete flag is set, so multiple versions of versioning do not occur.
Solution
If this error occurs, correct it in one of the ways below.
Reduce the number of records for UPDATE
By reducing the number of records that are changed at one time by an UPDATE statement, the amount of transaction logs created by one transaction is reduced.
When there are many records that need to be changed in one table
When performing UPDATE on multiple tables in one transaction
Since the TRX_UPDATE_MAX_LOGSIZE property is applied on a per-transaction basis, if multiple statements are used in one transaction as shown below, TRX_UPDATE_MAX_LOGSIZE may increase until COMMIT or ROLLBACK is performed.
Change TRX_UPDATE_MAX_LOGSIZE property setting value
The default value for the TRX_UPDATE_MAX_LOGSIZE property is 10M. If this value is too small, this can be changed at the session or system level.
First, this section will describe how to calculate the size of the transaction log that will be created in a specific transaction and the precautions for changing TRX_UPDATE_MAX_LOGSIZE, and then how to change it.
Calculation of the TRX_UPDATE_MAX_LOGSIZE value of the transaction
This is how to estimate the amount of transaction log to be used by a particular transaction to change TRX_UPDATE_MAX_LOGSIZE.
Session 1-UPDATE statement execution | Session 2-Query Performance View |
---|---|
Among the results of executing the query below, UPDATE_SIZE * 'Number of records to be updated' is the size of the transaction log that will be created in the transaction. | |
Note/Consideration on changing TRX_UPDATE_MAX_LOGSIZE
It is recommended to set TRX_UPDATE_MAX_LOGSIZE less than LOCK_ESCALATION_MEMORY_SIZE.
To prevent memory increase due to memory table MVCC, TRX_UPDATE_MAX_LOGSIZE and LOCK_ESCALATION_MEMORY_SIZE properties are provided.If the size of the transaction log created by one transaction exceeds TRX_UPDATE_MAX_LOGSIZE, the transaction is error-processed and rolled back. If LOCK_ESCALATION_MEMORY_SIZE is exceeded, the transaction's LOCK mode is switched from record level IX_LOCK to table level X LOCK.
- In the case of X LOCK, it may cause a problem in the service because the query transaction for the target table is also queued.
- To prevent this problem, set TRX_UPDATE_MAX_LOGSIZE to be smaller than LOCK_ESCALATION_MEMORY_SIZE so that X LOCK does not occur.
How to change
How to change property setting value
TRX_UPDATE_MAX_LOGSIZE should be set smaller than LOCK_ESCALATION_MEMORY_SIZE, so check both properties together.How to check the TRX_UPDATE_MAX_LOGSIZE and LOCK_ESCALATION_MEMORY_SIZE settingsHow to check the TRX_UPDATE_MAX_LOGSIZE setting value for each sessionHow to change session unit
This is applied to transactions performed after ALTER SESSION is executed.How to change the system level
After ALTER SYSTEM is executed, it is affected by the transaction performed in the new session. The value changed to ALTER SYSTEM is applied only while the Altibase server is running, and it returns to the default value when the Altibase server is restarted.In order to permanently reflect the changes to the Altibase server, change the altibase.properties file as well.
If the above has done, the changed value can be kept even if the Altibase server is restarted.
Reference
Related bug
In some versions, due to a bug related to TRX_UPDATE_MAX_LOGSIZE, it may not work properly even if the setting value of TRX_UPDATE_MAX_LOGSIZE is changed.
Altibase server version | Symptom | Bug |
---|---|---|
4.3.9.103 ~ 4.3.9.133 | The default value of TRX_UPDATE_MAX_LOGSIZE is 10M, but the value of the TRX_UPDATE_MAX_LOGSIZE column of V$SESSION is set to 0 (unlimited). | BUG-20511 |
4.3.9.163 ~ 4.3.9.165 | This is a bug that reads TRX_UPDATE_MAX_LOGSIZE incorrectly, and the error does not occur when an error should occur due to TRX_UPDATE_MAX_LOGSIZE. | BUG-26311 |