Minimal Datajoint Example

1. Shared database table

import datajoint as dj
from spyglass.utils.dj_mixin import SpyglassMixin

schema = dj.schema('sambray_transaction_debug')

@schema
class DummyInsert(SpyglassMixin, dj.Manual):
    definition = """
    # Dummy insert class
    id: int
    ---
    """

2. Process (1) begins a long-running transaction that cals insert1(xxx, skip_duplicates=True). Because skip_duplicates uses an update sql command, this puts a lock on the row that it is inserting until the end of the transaction

# Process 1
import time
with dj.conn().transaction:
    DummyInsert.insert1({'id': 1},skip_duplicates=True)
    time.sleep(300)

3. Process (2) attempts to do the same insert call as process (1) while the transaction from (1) is still occuring. Because the row is locked, it must wait for the transaction in (1) to end before it can proceed. This can result in timeout. Note that inserts into other rows or fetches from any row work during this time

# Process 2
DummyInsert.insert1({"id": 1}, skip_duplicates=True) #stalls/times out

Example Report From DummyInsert.check_threads during this lock

Spyglass Relevance

• The transaction protections in populate_all_common and single_transaction_make wrap many inserts into one, potentially long transaction.
• Many files will share common entry values for tables which use the skip_duplicates argument (ex. Lab.insert_from_nwbfile )
• Two files with shared values inserting at the same time will create the blocking lock

Solution

• Avoid use of skip_duplicates=True in the ingestion tables
• Since duplicates are expected (many files with same lab), instead first check for existing matching entry and then call insert only if not present
• Can integrate into the ingestion refactor PR: Standardize nwb ingestion #1377