Master database transactions and data consistency
Throughout this guide, we'll work with two collections: "Users" and "Cars", each containing one document.
{ "Age": 20, "Name": "Bob", "Car": { "Engine": 2, "Model": "Subaru" }, "Childs": [ { "Age": 3, "Name": "Ted", "Numbers": [4, 5, 6] }, { "Age": 5, "Name": "Mike", "Numbers": [7, 8, 9] } ], "Jobs": [ { "Company": "Microsoft", "Position": "Engineer" }, { "Company": "Google", "Position": "Engineer" }, { "Company": "Amazon", "Position": "Manager" } ], "Numbers": [1, 2, 3] }
{ "Company": "VW", "Car": "Touran", "Engine": "2.0" }
A transaction is a sequence of database operations that are treated as a single unit of work. Think of it like a shopping cart - you add items, review them, and then either complete the purchase (commit) or cancel everything (rollback).
Transactions follow the ACID principles:
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All operations succeed or all fail | If updating age fails, name update is also cancelled |
| Consistency | Database remains in valid state | Data relationships are preserved |
| Isolation | Transactions don't interfere with each other | Two users can update different records simultaneously |
| Durability | Committed changes are permanent | Data survives system crashes |
Fractal Platform supports three transaction isolation levels that control how transactions interact with each other:
The most basic isolation level. A transaction only sees data that has been committed by other transactions.
Client.BeginTran(TranType.ReadCommited);
| Pros | Cons |
|---|---|
| ✅ Prevents dirty reads ✅ Good performance ✅ High concurrency |
❌ Non-repeatable reads possible ❌ Phantom reads possible |
Ensures that if you read data twice in the same transaction, you'll get the same result. The data is locked for reading.
Client.BeginTran(TranType.RepeatableRead);
| Pros | Cons |
|---|---|
| ✅ Prevents dirty reads ✅ Prevents non-repeatable reads ✅ Data consistency guaranteed |
❌ Lower concurrency ❌ Possible lock conflicts ❌ Phantom reads possible |
Each transaction works with a snapshot of the database as it existed at the start of the transaction. No locks are needed for reading.
Client.BeginTran(TranType.Snapshot);
| Pros | Cons |
|---|---|
| ✅ No read locks needed ✅ Prevents all anomalies ✅ Best consistency ✅ Good for long transactions |
❌ Higher memory usage ❌ Possible update conflicts |
Every query in Fractal Platform runs in an implicit transaction. This means each operation is automatically committed:
// This operation runs in an implicit transaction var count = DocsWhere("Users", 1) .Count(); // Result: 1 (Bob's document exists)
For complex operations, you can create explicit transactions with full control:
// 1. Begin transaction Client.BeginTran(TranType.ReadCommited); // 2. Perform operations ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':30}"); // 3. Commit or rollback Client.CommitTran(); // Save changes // OR Client.RollbackTran(); // Cancel changes
When you commit a transaction, all changes become permanent:
Client.BeginTran(TranType.ReadCommited); // Update Bob's age ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':25}"); // Changes are visible inside transaction var users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); // users[0].Age == 25 Client.CommitTran(); // Changes are now permanent users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); // users[0].Age == 25 (still 25 after commit)
Rollback undoes all changes made during the transaction:
Client.BeginTran(TranType.ReadCommited); // Update Bob's age ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':30}"); // Changes are visible inside transaction var users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); // users[0].Age == 30 Client.RollbackTran(); // Changes are cancelled - original value restored users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); // users[0].Age == 20 (original value)
All three isolation levels support reading documents within a transaction:
// Read Committed Client.BeginTran(TranType.ReadCommited); var count = DocsWhere("Users", 1) .Count(); // Result: 1 (Bob exists) count = DocsWhere("Users", 555) .Count(); // Result: 0 (document 555 doesn't exist) Client.CommitTran();
Update data and save changes permanently:
Client.BeginTran(TranType.RepeatableRead); // Update Bob's age ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':20}"); // Verify inside transaction var users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); Assert.AreEqual(20, users[0].Age.Value); Client.CommitTran(); // Verify after commit - changes are permanent users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); Assert.AreEqual(20, users[0].Age.Value); // Age is still 20
Update data but cancel the changes:
Client.BeginTran(TranType.Snapshot); // Update Bob's age to 25 ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':25}"); // Verify inside transaction - shows new value var users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); Assert.AreEqual(25, users[0].Age.Value); Client.RollbackTran(); // Verify after rollback - original value restored users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); Assert.AreEqual(20, users[0].Age.Value); // Age is back to 20
When you add a document in a transaction and rollback, the document is not created:
Client.BeginTran(TranType.ReadCommited); // Add a new user AddDoc("Users", "{'Name':'Tim'}"); // Count inside transaction var count = DocsOf("Users") .Count(); Assert.AreEqual(2, count); // We see 2 documents: Bob and Tim Client.RollbackTran(); // Count after rollback count = DocsOf("Users") .Count(); Assert.AreEqual(1, count); // Back to 1 document: only Bob remains
When you add a document and commit, it becomes permanent:
Client.BeginTran(TranType.RepeatableRead); // Add a new user and save the ID var newDocID = AddDoc("Users", "{'Name':'Tim'}"); // Count inside transaction var count = DocsOf("Users") .Count(); Assert.AreEqual(2, count); Client.CommitTran(); // Count after commit count = DocsOf("Users") .Count(); Assert.AreEqual(2, count); // Still 2 documents - Tim was permanently added // Clean up - delete the new document DelDoc("Users", newDocID);
You can perform multiple operations on different documents:
Client.BeginTran(TranType.Snapshot); // Update Bob ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':25}"); // Add Tim AddDoc("Users", "{'Name':'Tim','Age':30}"); // Both operations are in the same transaction Client.CommitTran(); // Both changes are saved together
Transactions can span multiple collections. If rolled back, changes in all collections are cancelled:
Client.BeginTran(TranType.ReadCommited); // Add document to Users collection AddDoc("Users", "{'Name':'Tim'}"); // Add document to Cars collection AddDoc("Cars", "{'Model':'Tesla'}"); // Verify both collections inside transaction var userCount = DocsOf("Users") .Count(); Assert.AreEqual(2, userCount); // Users: Bob + Tim = 2 var carCount = DocsOf("Cars") .Count(); Assert.AreEqual(2, carCount); // Cars: VW Touran + Tesla = 2 Client.RollbackTran(); // Verify after rollback - both additions cancelled userCount = DocsOf("Users") .Count(); Assert.AreEqual(1, userCount); // Back to 1: only Bob carCount = DocsOf("Cars") .Count(); Assert.AreEqual(1, carCount); // Back to 1: only VW Touran
When committing, all changes across all collections are saved together:
Client.BeginTran(TranType.RepeatableRead); // Add to Users var newUserID = AddDoc("Users", "{'Name':'Tim'}"); // Add to Cars var newCarID = AddDoc("Cars", "{'Model':'Tesla'}"); // Verify inside transaction var userCount = DocsOf("Users") .Count(); Assert.AreEqual(2, userCount); var carCount = DocsOf("Cars") .Count(); Assert.AreEqual(2, carCount); Client.CommitTran(); // Verify after commit - both additions permanent userCount = DocsOf("Users") .Count(); Assert.AreEqual(2, userCount); carCount = DocsOf("Cars") .Count(); Assert.AreEqual(2, carCount); // Clean up DelDoc("Users", newUserID); DelDoc("Cars", newCarID);
With Read Committed, transactions see committed data from other transactions:
// Initial state: Bob's age is 25 DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':25}"); // Client 1: Start transaction Client.BeginTran(TranType.ReadCommited); // Client 1: Update Bob's age to 20 DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':20}"); // Client 1: Sees their own changes var users = DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); Assert.AreEqual(20, users[0].Age.Value); // Client 2: Sees old committed data (25) users = otherClient.DocsWhere("Users", "{'Name':'Bob'}") .Select<UserInfo>(); Assert.AreEqual(25, users[0].Age.Value); // Client 1: Rollback changes Client.RollbackTran();
Repeatable Read locks data for reading. Other transactions cannot modify locked data:
// Set error handling for locks TranManager.IsRaiseErrorOnLock = true; // Client 1: Start Repeatable Read transaction Client.BeginTran(TranType.RepeatableRead); // Client 1: Read Bob's age (locks the data) var age = DocsWhere("Users", "{'Name':'Bob'}") .Value("{'Age':$}"); Assert.AreEqual("25", age); // Client 2: Try to update locked data - FAILS otherClient.DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':20}"); Assert.AreEqual(true, otherClient.Context.HasError); // Update blocked because Client 1 has a read lock otherClient.Context.ResetError(); // Client 1: Commit and release lock Client.CommitTran(); // Client 2: Now update succeeds otherClient.DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':20}"); age = DocsWhere("Users", "{'Name':'Bob'}") .Value("{'Age':$}"); Assert.AreEqual("20", age);
Snapshot isolation prevents write conflicts but allows concurrent reads:
// Initial state: Bob's age is 25 DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':25}"); // Client 1: Start Snapshot transaction Client.BeginTran(TranType.Snapshot); // Client 2: Try to update - FAILS (write lock) otherClient.DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':20}"); Assert.AreEqual(true, otherClient.Context.HasError); otherClient.Context.ResetError(); // Client 1: Update data in snapshot DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':20}"); // Client 1: Commit Client.CommitTran(); // Client 2: Can now read updated value var age = otherClient.DocsWhere("Users", "{'Name':'Bob'}") .Value("{'Age':$}"); Assert.AreEqual("20", age);
| Use Case | Recommended Level | Reason |
|---|---|---|
| Simple reads | Read Committed | Fast, good concurrency |
| Financial calculations | Repeatable Read | Prevents data changes during calculation |
| Long reports | Snapshot | Consistent view without blocking |
| Batch updates | Read Committed | Balance between consistency and performance |
// ❌ BAD - Long transaction Client.BeginTran(TranType.RepeatableRead); var users = DocsOf("Users").Select<UserInfo>(); // ... lots of processing ... // ... user interaction ... ModifyDocsWhere("Users", "{'Name':'Bob'}").Update("{'Age':30}"); Client.CommitTran(); // ✅ GOOD - Short transaction var users = DocsOf("Users").Select<UserInfo>(); // ... processing outside transaction ... Client.BeginTran(TranType.RepeatableRead); ModifyDocsWhere("Users", "{'Name':'Bob'}").Update("{'Age':30}"); Client.CommitTran();
// ✅ GOOD - Proper error handling try { Client.BeginTran(TranType.RepeatableRead); ModifyDocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':30}"); AddDoc("Cars", "{'Model':'Tesla'}"); Client.CommitTran(); } catch (Exception ex) { Client.RollbackTran(); // Log error and handle appropriately }
Always test your application with multiple simultaneous users to identify potential concurrency issues:
// Create a second client for testing var context = CreateUserContext(); var otherClient = CreateClient(context, Instance); // Test concurrent operations Client.BeginTran(TranType.RepeatableRead); // ... Client 1 operations ... // Attempt conflicting operation from Client 2 otherClient.DocsWhere("Users", "{'Name':'Bob'}") .Update("{'Age':40}"); // Check for conflicts if (otherClient.Context.HasError) { // Handle the error appropriately }
Always document which isolation level you're using and why:
// Using Repeatable Read to ensure consistent totals // during financial report generation Client.BeginTran(TranType.RepeatableRead); var total = CalculateOrderTotal(); Client.CommitTran();