Transactions

Databases are not merely storage systems; they should accurately represent an enterprise’s current state. This means that all users, irrespective of their interactions, should view and engage with the same data simultaneously, seeing the results of each other’s interactions without breaking data integrity. This principle is known as data consistency.

Understanding data consistency becomes clearer when examining its breaches. For instance, during early morning hours, I’ve observed my bank’s website displaying the previous day’s pending transactions, but the account balance doesn’t reflect these changes until a couple of hours later. This discrepancy between transaction views and account balances exemplifies data inconsistency. Fortunately, such inconsistencies, in this case, seem to be confined to the web interface, as the system eventually reaches a consistent state.

Ensuring data consistency is straightforward in certain scenarios. By avoiding conditions that might compromise it, consistency is preserved. For example, if only one party generates data and the rest merely access it, the likelihood of conflicts leading to inconsistency is minimal. Delayed queries still provide a consistent, albeit older, state. This is typical in scientific projects, where one lab produces data while others analyze it.

Complexities arise when multiple entities, be they human or digital, access and modify data simultaneously. Maintaining consistency amidst such concurrent interactions becomes challenging. To achieve this, databases might temporarily limit access for some users during another’s transaction or force users to resolve discrepancies before data integration.

The ACID Model¶

Modern relational databases adhere to the ACID model to maintain consistency:

Ensuring consistency becomes notably challenging in geographically dispersed systems with distributed data storage, especially when faced with slow or intermittent network connections. Historically, it was believed that data systems spanning vast areas couldn’t maintain consistency. The CAP Theorem suggested that in such systems, there’s an irreconcilable trade-off between system responsiveness (availability) and data consistency.

Traditional relational database systems, like Oracle, MySQL, and others, maintained strong consistency but weren’t tailored for distributed setups. This limitation spurred the rise of NoSQL in the 2000s and 2010s, emphasizing responsiveness in distributed systems, albeit with weaker consistency.

However, recent advancements have bridged this gap. Modern distributed systems, like Spanner and CockroachDB, leverage data replication and consensus algorithms (e.g., Paxos, Raft) to offer high availability while maintaining strict consistency.

DataJoint adheres to the classic ACID consistency model, leveraging serializable transactions for operations that must be atomic.

Example: Bank Transfers¶

The classic example for understanding transactions is a bank transfer. When transferring money between accounts, two operations must happen together:

1. Debit the source account

2. Credit the destination account

If only one operation completes (due to an error, network failure, or system crash), money is either lost or created out of thin air—both unacceptable outcomes.

Let’s set up a simple bank account schema to demonstrate:

The Problem: Transfer Without Transactions¶

Here’s a naive transfer function that doesn’t use transactions. If an error occurs between the debit and credit operations, money disappears:

The Solution: DataJoint Transactions¶

DataJoint provides a transaction context manager through the connection object. Operations inside the with conn.transaction: block are atomic—if any operation fails, all changes are rolled back automatically.

The Transaction Context Manager¶

DataJoint’s transaction context manager (conn.transaction) provides:

conn = dj.conn()

with conn.transaction:
    # All operations here are part of one atomic transaction
    Table1.insert(...)
    Table2.insert(...)
    Table3.delete(...)
# If we reach here, all operations are committed

Behavior:

• On success: When the with block completes normally, all changes are committed to the database.

• On exception: If any exception is raised inside the block, all changes are rolled back and the exception is re-raised.

A Working Transfer Function¶

Here’s a complete, working transfer function:

Transactions in Computed Tables¶

An important feature of DataJoint is that make() methods in Computed and Imported tables automatically run inside a transaction. This means:

1. All inserts within a single make() call are atomic

2. If make() raises an exception, no partial results are inserted

3. The computation either fully succeeds or fully fails

@schema
class ProcessedData(dj.Computed):
    definition = """
    -> RawData
    ---
    result : float
    """
    
    def make(self, key):
        # Everything here runs inside a transaction
        data = (RawData & key).fetch1("data")
        result = expensive_computation(data)
        
        self.insert1(dict(key, result=result))
        # If we crash here, the insert is rolled back
        
        AuxiliaryTable.insert1(...)  # Also part of the same transaction

This automatic transaction wrapping is why DataJoint pipelines maintain computational validity—downstream results are always consistent with their upstream inputs.

Transactions and Master-Part Relationships¶

Master-part tables (see Master-Part) leverage transactions to ensure that a master entry and all its parts are inserted atomically.

When inserting into a master table with parts:

@schema 
class Experiment(dj.Manual):
    definition = """
    experiment_id : int
    ---
    experiment_date : date
    """
    
    class Trial(dj.Part):
        definition = """
        -> master
        trial_num : int
        ---
        outcome : varchar(20)
        """

# Insert master and parts together in a transaction
conn = dj.conn()
with conn.transaction:
    Experiment.insert1(dict(experiment_id=1, experiment_date='2024-01-15'))
    Experiment.Trial.insert([
        dict(experiment_id=1, trial_num=1, outcome='success'),
        dict(experiment_id=1, trial_num=2, outcome='failure'),
        dict(experiment_id=1, trial_num=3, outcome='success'),
    ])

This ensures you never have an experiment without its trials, or orphaned trials without an experiment.

Best Practices¶

When to Use Explicit Transactions¶

A single insert() or update1() call is already atomic—it either fully succeeds or fully fails. Explicit transactions using conn.transaction are only needed when multiple operations must succeed or fail together:

# No need for explicit transaction - single insert is already atomic
Table.insert(data)

# Explicit transaction needed - multiple operations must be atomic
with dj.conn().transaction:
    Table1.insert(data1)
    Table2.insert(data2)
    Table3.update1(data3)

Transactions and Long Computations¶

When computations take a long time, there’s a tension between keeping transactions short (to avoid blocking) and maintaining data integrity (ensuring inputs haven’t changed).

DataJoint’s make() method handles this through a three-part pattern that refetches and verifies input data inside the transaction. See The make Method for details on implementing safe computations.

Avoid User Interaction Inside Transactions¶

Never wait for user input while holding a transaction open:

# Bad: User could take coffee break!
with conn.transaction:
    Table.insert(data)
    confirm = input("Confirm? (y/n)")  # DON'T DO THIS
    if confirm != 'y':
        raise Exception("Cancelled")

Handle Deadlocks Gracefully¶

When multiple transactions compete for the same resources, deadlocks can occur. MySQL automatically detects and resolves deadlocks by rolling back one transaction.

import time

def safe_operation(max_retries=3):
    """Execute operations with deadlock retry logic."""
    for attempt in range(max_retries):
        try:
            with dj.conn().transaction:
                Table1.insert(data1)
                Table2.insert(data2)
            return  # Success
        except Exception as e:
            if "Deadlock" in str(e) and attempt < max_retries - 1:
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
                continue
            raise

Summary¶

Transactions are fundamental to maintaining data integrity in DataJoint:

Key takeaways:

1. Use explicit transactions only when multiple operations must succeed or fail together

2. Single inserts and updates are already atomic

3. DataJoint’s populate() automatically handles transactions for you

4. Master-part relationships provide built-in transactional semantics

5. For long computations, see The make Method for safe patterns