LogoLogo
BlogDiscordGitHubDoltgres
  • Introduction
    • What is Doltgres?
    • Installation
    • Getting Started
  • Concepts
    • Git
      • Commits
      • Log
      • Diff
      • Branch
      • Merge
      • Conflicts
      • Remotes
      • Working Set
    • SQL
      • Databases
      • Schema
      • Tables
      • Primary Keys
      • Types
      • Indexes
      • Views
      • Constraints
      • Triggers
      • Functions
      • Procedures
      • Users/Grants
      • Transactions
      • System Variables
    • RDBMS
      • Server
      • Backups
      • Replication
  • Guides
    • Cheat Sheet
    • Replication from Postgres
  • Reference
    • Running the Server
      • Configuration
      • Access Management
      • Branch Permissions
      • Backups
      • Garbage Collection
      • Metrics
      • Replication
      • Troubleshooting
    • Version Control Features
      • Using Branches
      • Merges
      • Querying History
      • Using Remotes
      • Functions
      • System Tables
      • System Variables
    • SQL Language Support
      • Supported Functions and Operators
      • Supported Types
      • Supported SQL Commands
      • System Catalog Schema
    • Supported Clients
      • Programmatic
    • Benchmarks
      • Correctness
      • Latency
Powered by GitBook
On this page
  • What is a Transaction?
  • How to use Transactions
  • Difference between Postgres Transaction and Doltgres Transaction
  • Interaction with Doltgres Version Control
  • Example
  1. Concepts
  2. SQL

Transactions

PreviousUsers/GrantsNextSystem Variables

Last updated 11 months ago

What is a Transaction?

A transaction is the unit of change isolation is a database.

How to use Transactions

Transactions are generally used to manage concurrent writes to the same data in a database. If two writers attempt to write the same data, one writer is allowed to commit their transaction and the other is rolled back. Changes being made in a transaction can only be seen by the current session.

A transaction is started with a BEGIN statement. A transaction is ended with a COMMIT or ROLLBACK statement. A commit persists the changes made. A rollback puts the state of the database back to the way it was when the transaction began.

Postgres and Doltgres both automatically commit every statement that wasn't explicitly placed in a transaction with a BEGIN statement.

Difference between Postgres Transaction and Doltgres Transaction

Doltgres uses the Read Committed transaction model whereas Postgres supports .

Interaction with Doltgres Version Control

Traditional SQL transactions exist in isolation from Doltgres version control features.

Doltgres can be thought of having two layers of transactions. The first layer accessed with BEGIN and COMMIT SQL statements is the same as Postgres. Doltgres adds an additional second layer with branches and Doltgres commits. Branches can be thought of as long running transactions that may or may not be merged.

Note, you can make every transaction COMMIT a Doltgres commit by setting the ,

Example

BEGIN;
select * from docs;
+----+----+
| pk | c1 |
+----+----+
| 0  | 0  |
| 1  | 1  |
| 2  | 2  |
| 3  | 0  |
| 4  | 4  |
+----+----+
delete from docs where pk=4;
select * from docs;
+----+----+
| pk | c1 |
+----+----+
| 0  | 0  |
| 1  | 1  |
| 2  | 2  |
| 3  | 0  |
+----+----+
database=# rollback;
database=# select * from docs;
+----+----+
| pk | c1 |
+----+----+
| 0  | 0  |
| 1  | 1  |
| 2  | 2  |
| 3  | 0  |
| 4  | 4  |
+----+----+
all transaction isolation levels
system variable
@@dolt_transaction_commit