Inserting data into an allpages-locked heap table
where the data needs to be inserted.
If the last page is full, a new page is allocated in the current extent and linked onto the chain. If the extent is full, Adaptive Server looks for empty pages on other extents being used by the table. If no pages are available, a new extent is allocated to the table.
Conflicts during heap inserts
If many users are trying to insert into an allpages-locked heap table at the same time, each insert must
wait for the preceding transaction to complete.
This problem of last-page conflicts on heaps is true for:
• Single row inserts using insert
• Multiple row inserts using select into or insert…select, or several insert statements in a batch
• Bulk copy into the table
Some workarounds for last-page conflicts on heaps include:
• Switching to datapages or datarows locking
• Creating a clustered index that directs the inserts to different pages
• Partitioning the table, which creates multiple insert points for the table, giving you multiple “last pages” in an allpages-locked table