How the FSCR search mechanism works when several threads are inserting data?

Body

Before data is inserted into a table, an insert search algorithm examines the free space control records (FSCRs) to find a page with enough space for the new data.
But how the FSCR search mechanism works when several threads are inserting – does each thread do it’s own independent search (ie. potentially searching in parallel so theoretically more threads would be faster, discounting other contention), or will the threads queue and search one at a time (therefore more threads would simply queue and therefore not be any faster)?

Ans:

If memory serves me correctly (from v10.1+), in non-Purescale, FSCR cache size is 1 page. Concurrent inserts will all use the same FSCR cache per table and end up going to the same page to look for space. In Purescale, the FSCR cache is generally 1 extent per member. Concurrent inserts will potentially do 2 rounds of searching through the cache. The first time, pages are latched conditionally. If case of a latch conflict, move on to the next page. If a second round is required, page latching is done unconditionally. An attempt is made to prevent all inserts from starting on the same page in the cache -- I believe the starting page within the cache is determined by tid%cache size. If the table is of sufficient size, each member gets a unique set of pages.

For append mode tables, we have dual append caches and the number of pages within the cache is based on the number of concurrent inserts. The larger the number of concurrent inserts, the larger the cache size. I think the max cache size if 1 extent. When all pages in the current cache is full, the insert that runs into this condition will switch the current and next caches and allocate new pages in the new next cache.

For a non append mode table, if the max number of FSCRs is searched and no space is found, the table is put into temporary append mode. In temporary append mode, the cache can have multiple pages based on number of concurrent inserts.