Incomplete ranks shown while CAD is running

We have the manually-triggered background job for computing ranks information (CAD). For each of the ranks tables, it removes all rows, then computes and inserts fresh rows. While the computation is running, the ranks tables have missing information, which surfaces in the UI and API.

Here are a few screenshots while running CAD on the staging, where it can be seen how CAD progresses:

Screenshots

Records showing only average as ConciseSingleResults are being computed
Records showing only single as ConciseAverageResults are being computed
Incremental entries in the profile as RanksSingle are being computed

The same issue affects API endpoints that rely on ranks (records, WCIF personal bests), and that is a bigger problem, because API users definitely don't expect records to suddenly have no entries.

For more context, I believe this is the underlying issue behind a few strange false-positive record tags we've seen on WCA Live thewca/wca-live#145.

Note that some of the pages/endpoints are cached, so keep that in mind when reproducing.

Interesting... Taking a complete shot in the dark here but would wrapping the whole CAD process into a transaction help?

We already update each table in a transaction, so it should be atomic:

worldcubeassociation.org/lib/auxiliary_data_computation.rb

Lines 15 to 19 in 1e83c26

    
                 ActiveRecord::Base.transaction do 
        
                   ActiveRecord::Base.connection.execute "DELETE FROM #{table_name}" 
        
                   ActiveRecord::Base.connection.execute "ALTER TABLE #{table_name} AUTO_INCREMENT = 1" 
        
                   ActiveRecord::Base.connection.execute <<-SQL 
        
                     INSERT INTO #{table_name} (id, #{field}, valueAndId, personId, eventId, countryId, continentId, year, month, day)

Just looking at the code I don't have any reasonable idea, unless the queries skip the transaction somehow. One option to test would be to add rollback after the delete and see if that works as expected.

#9742 is an example of a fix for this issue which would help us determine if the issue is indeed the in-progress table being read while CAD is running.

The other approach was to increase the isolation level of the CAD transactions, but that seems like a very bad idea because it can lead to database locks etc.

I believe we can close this now, thanks @dunkOnIT :)

	ActiveRecord::Base.transaction do
	ActiveRecord::Base.connection.execute "DELETE FROM #{table_name}"
	ActiveRecord::Base.connection.execute "ALTER TABLE #{table_name} AUTO_INCREMENT = 1"
	ActiveRecord::Base.connection.execute <<-SQL
	INSERT INTO #{table_name} (id, #{field}, valueAndId, personId, eventId, countryId, continentId, year, month, day)