Deadlocks on sample/prep information creation

[ElDeveloper]

While running a tutorial yesterday, we found that when lots of users submitted their sample information or prep information to their study, the database would deadlock and show this message:

In the sample information:

Error running SQL query:
Query: CREATE TABLE qiita.sample_10976 (
sample_id varchar NOT NULL, acne_medication varchar, acne_medication_otc varchar, add_adhd varchar, age_cat varchar, age_corrected varchar, age_years varchar, alcohol_consumption varchar, alcohol_frequency varchar, alcohol_types varchar, alcohol_types_beercider varchar, alcohol_types_red_wine varchar, alcohol_types_sour_beers varchar, alcohol_types_spiritshard_alcohol varchar, alcohol_types_unspecified varchar, alcohol_types_white_wine varchar, allergic_to varchar, allergic_to_i_have_no_food_allergies_that_i_know_of varchar, allergic_to_other varchar, allergic_to_peanuts varchar, allergic_to_shellfish varchar, allergic_to_tree_nuts varchar, allergic_to_unspecified varchar, alzheimers varchar, anonymized_name varchar, antibiotic_history varchar, appendix_removed varchar, asd varchar, assigned_from_geo varchar, autoimmune varchar, birth_year varchar, bmi varchar, bmi_cat varchar, bmi_corrected varchar, body_habitat varchar, body_product varchar, body_site varchar, bowel_movement_frequency varchar, bowel_movement_quality varchar, breastmilk_formula_ensure varchar, cardiovascular_disease varchar, cat varchar, cdiff varchar, census_region varchar, chickenpox varchar, clinical_condition varchar, collection_date varchar, collection_month varchar, collection_season varchar, collection_time varchar, collection_timestamp varchar, consume_animal_products_abx varchar, contraceptive varchar, cosmetics_frequency varchar, country varchar, country_of_birth varchar, csection varchar, deodorant_use varchar, depression_bipolar_schizophrenia varchar, description varchar, diabetes varchar, diet_type varchar, dna_extracted varchar, dog varchar, dominant_hand varchar, drinking_water_source varchar, drinks_per_session varchar, economic_region varchar, elevation varchar, env_biome varchar, env_feature varchar, env_material varchar, env_package varchar, epilepsy_or_seizure_disorder varchar, exercise_frequency varchar, exercise_location varchar, fed_as_infant varchar, fermented_plant_frequency varchar, flossing_frequency varchar, flu_vaccine_date varchar, frozen_dessert_frequency varchar, fruit_frequency varchar, fungal_overgrowth varchar, geo_loc_name varchar, gluten varchar, height_cm varchar, height_units varchar, high_fat_red_meat_frequency varchar, homecooked_meals_frequency varchar, host_common_name varchar, host_subject_id varchar, host_taxid varchar, ibd varchar, ibd_diagnosis varchar, ibd_diagnosis_refined varchar, ibs varchar, kidney_disease varchar, lactose varchar, last_move varchar, last_travel varchar, latitude varchar, level_of_education varchar, liver_disease varchar, livingwith varchar, longitude varchar, lowgrain_diet_type varchar, lung_disease varchar, meat_eggs_frequency varchar, migraine varchar, milk_cheese_frequency varchar, milk_substitute_frequency varchar, multivitamin varchar, nail_biter varchar, non_food_allergies varchar, non_food_allergies_beestings varchar, non_food_allergies_drug_eg_penicillin varchar, non_food_allergies_pet_dander varchar, non_food_allergies_poison_ivyoak varchar, non_food_allergies_sun varchar, non_food_allergies_unspecified varchar, olive_oil varchar, one_liter_of_water_a_day_frequency varchar, other_supplement_frequency varchar, physical_specimen_location varchar, physical_specimen_remaining varchar, pku varchar, pool_frequency varchar, poultry_frequency varchar, pregnant varchar, prepared_meals_frequency varchar, probiotic_frequency varchar, public varchar, race varchar, ready_to_eat_meals_frequency varchar, red_meat_frequency varchar, roommates varchar, roommates_in_study varchar, salted_snacks_frequency varchar, sample_type varchar, scientific_name varchar, seafood_frequency varchar, seasonal_allergies varchar, sex varchar, sibo varchar, skin_condition varchar, sleep_duration varchar, smoking_frequency varchar, softener varchar, specialized_diet varchar, state varchar, subset_age varchar, subset_antibiotic_history varchar, subset_bmi varchar, subset_diabetes varchar, subset_healthy varchar, subset_ibd varchar, sugar_sweetened_drink_frequency varchar, sugary_sweets_frequency varchar, survey_id varchar, taxon_id varchar, teethbrushing_frequency varchar, thyroid varchar, title varchar, tonsils_removed varchar, types_of_plants varchar, vegetable_frequency varchar, vioscreen_a_bev varchar, vioscreen_a_cal varchar, vioscreen_acesupot varchar, vioscreen_activity_level varchar, vioscreen_add_sug varchar, vioscreen_addsugar varchar, vioscreen_adsugtot varchar, vioscreen_age varchar, vioscreen_alanine varchar, vioscreen_alcohol varchar, vioscreen_alcohol_servings varchar, vioscreen_alphacar varchar, vioscreen_alphtoce varchar, vioscreen_alphtoco varchar, vioscreen_arginine varchar, vioscreen_ash varchar, vioscreen_aspartam varchar, vioscreen_aspartic varchar, vioscreen_avcarb varchar, vioscreen_bcodeid varchar, vioscreen_betacar varchar, vioscreen_betacryp varchar, vioscreen_betaine varchar, vioscreen_betatoco varchar, vioscreen_biochana varchar, vioscreen_bmi varchar, vioscreen_caffeine varchar, vioscreen_calcium varchar, vioscreen_calcium_avg varchar, vioscreen_calcium_dose varchar, vioscreen_calcium_freq varchar, vioscreen_calcium_from_dairy_servings varchar, vioscreen_calcium_servings varchar, vioscreen_calories varchar, vioscreen_carbo varchar, vioscreen_cholest varchar, vioscreen_choline varchar, vioscreen_clac9t11 varchar, vioscreen_clat10c12 varchar, vioscreen_copper varchar, vioscreen_coumest varchar, vioscreen_cystine varchar, vioscreen_d_cheese varchar, vioscreen_d_milk varchar, vioscreen_d_tot_soym varchar, vioscreen_d_total varchar, vioscreen_d_yogurt varchar, vioscreen_daidzein varchar, vioscreen_database varchar, vioscreen_delttoco varchar, vioscreen_discfat_oil varchar, vioscreen_discfat_sol varchar, vioscreen_dob varchar, vioscreen_eer varchar, vioscreen_email varchar, vioscreen_erythr varchar, vioscreen_f_citmlb varchar, vioscreen_f_nj_citmlb varchar, vioscreen_f_nj_other varchar, vioscreen_f_nj_total varchar, vioscreen_f_other varchar, vioscreen_f_total varchar, vioscreen_fat varchar, vioscreen_fiber varchar, vioscreen_fibh2o varchar, vioscreen_fibinso varchar, vioscreen_finished varchar, vioscreen_fish_servings varchar, vioscreen_fol_deqv varchar, vioscreen_fol_nat varchar, vioscreen_fol_syn varchar, vioscreen_formontn varchar, vioscreen_fried_fish_servings varchar, vioscreen_fried_food_servings varchar, vioscreen_frt5_day varchar, vioscreen_frtsumm varchar, vioscreen_fructose varchar, vioscreen_fruit_servings varchar, vioscreen_galactos varchar, vioscreen_gammtoco varchar, vioscreen_gender varchar, vioscreen_genistn varchar, vioscreen_glac varchar, vioscreen_gltc varchar, vioscreen_glucose varchar, vioscreen_glutamic varchar, vioscreen_glycine varchar, vioscreen_glycitn varchar, vioscreen_grams varchar,
CONSTRAINT fk_sample_10976 FOREIGN KEY (sample_id)
REFERENCES qiita.study_sample (sample_id)
ON UPDATE CASCADE
)
Arguments: None
Error: deadlock detected
DETAIL: Process 30976 waits for AccessExclusiveLock on relation 46602 of database 28892; blocked by process 30899.
Process 30899 waits for AccessExclusiveLock on relation 46602 of database 28892; blocked by process 30976.
HINT: See server log for query details.

In the prep:

 Error running SQL query: Query: CREATE TABLE qiita.prep_2759 ( sample_id
varchar NOT NULL, barcode varchar, center_name varchar, center_project_name
varchar, experiment_center varchar, experiment_design_description varchar,
experiment_title varchar, extraction_robot varchar, extractionkit_lot
varchar, instrument_model varchar, library_construction_protocol varchar,
linker varchar, mastermix_lot varchar, orig_name varchar,
original_run_prefix varchar, pcr_primers varchar, platform varchar, plating
varchar, primer varchar, primer_date varchar, primer_plate varchar,
processing_robot varchar, project_name varchar, run_center varchar,
run_date varchar, run_prefix varchar, samp_size varchar, sample_plate
varchar, sequencing_meth varchar, target_gene varchar, target_subfragment
varchar, tm1000_8_tool varchar, tm300_8_tool varchar, tm50_8_tool varchar,
water_lot varchar, well_description varchar, well_id varchar, CONSTRAINT
fk_prep_2759 FOREIGN KEY (sample_id) REFERENCES qiita.study_sample
(sample_id) ON UPDATE CASCADE ) Arguments: None Error: deadlock detected
DETAIL: Process 39640 waits for AccessExclusiveLock on relation 46602 of
database 28892; blocked by process 39642. Process 39642 waits for
AccessExclusiveLock on relation 46602 of database 28892; blocked by process
39640. HINT: See server log for query details.

The fix is rather simple, just try again and this should be fine.

[antgonza]

Thanks for reporting. The issue is: REFERENCES qiita.study_sample (sample_id), some good readings: https://dba.stackexchange.com/a/82003 https://www.postgresql.org/docs/9.1/static/explicit-locking.html

[antgonza]

As stated in my previous comment the issue was the FK so this was solved when we removed all of those in https://github.com/biocore/qiita/blob/master/qiita_db/support_files/patches/python_patches/55.py