Allowing Concurrent Inserts
Data is “inserted” into a Vector table by either an insert or an append. An insert goes to the PDTs, which reside in memory. An append is written directly to the table files on disk. (PDTs eventually are written to disk through update propagation.)
The default behavior is: Single row inserts go through the PDTs (insert), INSERT AS SELECT goes directly to disk (append).
If you need to do concurrent INSERT AS SELECTs into the same table, you must set the insert mode to ROW. The insert mode can be changed at the session level by using the SET INSERTMODE statement or at the installation level by setting the DBMS configuration parameter x100_insertmode.
At the session level, if you do not use SET INSERTMODE ROW, then by default, a second commit will fail with the following error:
E_VW1120 Error committing transaction: conflict appending to table - concurrent append or update.
When you SET INSERTMODE to ROW in both sessions, however, the second commit will succeed.
The insert mode can be set to BULK, which means data is appended directly to disk, which does not allow concurrent inserts. If you have two sessions and at least one of them has the insert mode set to BULK, then an error will occur.
Note: The insert mode also applies to the MERGE operation.
Last modified date: 03/21/2024