Transactions

Ensure data integrity with atomic database operations

What are Transactions?

Transactions ensure that a series of database operations either all succeed or all fail together. This guarantees data consistency and integrity, especially when multiple related operations must be atomic.

Transaction Methods

RoomSharp provides several methods for running code within transactions:

Synchronous Transactions

// No return value
db.RunInTransaction(() =>
{
    db.UserDao.Insert(user1);
    db.UserDao.Insert(user2);
});

// With return value
var result = db.RunInTransaction(() =>
{
    var userId = db.UserDao.Insert(user);
    db.ProfileDao.Insert(new Profile { UserId = userId });
    return userId;
});

Asynchronous Transactions

// No return value
await db.RunInTransactionAsync(async () =>
{
    await db.UserDao.InsertAsync(user1);
    await db.UserDao.InsertAsync(user2);
});

// With return value
var userId = await db.RunInTransactionAsync(async () =>
{
    var id = await db.UserDao.InsertAsync(user);
    await db.ProfileDao.InsertAsync(new Profile { UserId = id });
    return id;
});

The `[Transaction]` Attribute

For cleaner code, mark DAO methods with [Transaction]. The source generator will automatically wrap the method body in transaction handling.

[Dao]
public interface IUserDao
{
    [Insert]
    long Insert(User user);

    [Query("SELECT * FROM users WHERE Email = :email")]
    Task<User?> FindByEmailAsync(string email);

    [Update]
    int Update(User user);

    // Transactional method with implementation
    [Transaction]
    async Task<long> UpsertAsync(User user)
    {
        var existing = await FindByEmailAsync(user.Email);
        if (existing is null)
        {
            return Insert(user);
        }
        
        existing.Name = user.Name;
        existing.UpdatedAt = DateTime.UtcNow;
        Update(existing);
        return existing.Id;
    }
}

How It Works

When you use [Transaction], the source generator produces code similar to:

C# - Generated Code

// Generated implementation
public async Task<long> UpsertAsync(User user)
{
    return await _database.RunInTransactionAsync(async () =>
    {
        var existing = await FindByEmailAsync(user.Email);
        if (existing is null)
        {
            return Insert(user);
        }
        
        existing.Name = user.Name;
        existing.UpdatedAt = DateTime.UtcNow;
        Update(existing);
        return existing.Id;
    });
}

Complex Transaction Example

[Dao]
public interface ITodoDao
{
    [Insert]
    long Insert(Todo todo);

    [Delete]
    int Delete(Todo todo);

    [Query("DELETE FROM todos WHERE IsCompleted = 1")]
    Task<int> DeleteCompletedAsync();

    [Query("SELECT * FROM todos WHERE ListId = :listId")]
    Task<List<Todo>> GetByListAsync(long listId);

    // Complex transactional operation
    [Transaction]
    async Task<int> ReplaceAllAsync(long listId, IEnumerable<Todo> newTodos)
    {
        // Step 1: Delete existing todos in the list
        var existing = await GetByListAsync(listId);
        foreach (var todo in existing)
        {
            Delete(todo);
        }
        
        // Step 2: Insert new todos
        var count = 0;
        foreach (var todo in newTodos)
        {
            todo.ListId = listId;
            Insert(todo);
            count++;
        }
        
        return count;
    }
}

Transaction Behavior

ACID Properties

Atomicity - All operations succeed or all fail
Consistency - Database remains in valid state
Isolation - Concurrent transactions don't interfere
Durability - Committed changes persist

Rollback on Exception

If any operation within a transaction throws an exception, the entire transaction is rolled back:

try
{
    await db.RunInTransactionAsync(async () =>
    {
        await db.UserDao.InsertAsync(user);
        await db.ProfileDao.InsertAsync(profile);
        
        // If this throws, both inserts are rolled back
        await db.SettingsDao.InsertAsync(settings);
    });
}
catch (Exception ex)
{
    // Transaction was rolledback
    _logger.LogError(ex, "Transaction failed");
}

Nested Transactions

RoomSharp handles nested transactions automatically. Inner transactions become part of the outer transaction:

[Transaction]
async Task<long> CreateUserWithProfileAsync(User user, Profile profile)
{
    var userId = await UpsertAsync(user); // This is also [Transaction]
    profile.UserId = userId;
    await _profileDao.InsertAsync(profile);
    return userId;
}

💡 Note: The inner transaction (UpsertAsync) runs within the context of the outer transaction (CreateUserWithProfileAsync). If either fails, both roll back.

Transaction vs Individual Operations

Scenario	Without Transaction	With Transaction
Multiple inserts	Each commits separately	All or nothing
Error halfway through	Partial data saved	Full rollback
Performance	Multiple disk writes	Single commit
Concurrent access	May see intermediate state	Isolated changes

Best Practices

✅ Do

Use transactions for operations that must be atomic
Keep transactions short to minimize lock contention
Use [Transaction] attribute for clean, readable code
Always handle exceptions properly
Use async transactions for I/O-bound operations

❌ Don't

Don't keep transactions open during user interaction
Don't perform long-running operations inside transactions
Don't mix transaction methods incorrectly (sync/async)
Don't catch and ignore exceptions in transactions

Performance Considerations

Transactions can improve performance for bulk operations by reducing the number of disk writes:

// Slow: Each insert commits separately
foreach (var user in users)
{
    await db.UserDao.InsertAsync(user);
}

// Fast: Single transaction, one commit
await db.RunInTransactionAsync(async () =>
{
    foreach (var user in users)
    {
        await db.UserDao.InsertAsync(user);
    }
});

⚡ Performance Tip: For even better bulk insert performance, use the Batch Insert Engine which combines transactions with zero-allocation optimizations.

Next Steps

Migrations
Batch Insert Engine - High-performance bulk operations
Query API - Learn about query execution
Error Handling - Handle database errors properly