home / skills / pluginagentmarketplace / custom-plugin-sql / transactions

transactions skill

safe

This skill helps you implement ACID transactions, manage isolation levels, and handle concurrency in SQL backed applications.

npx playbooks add skill pluginagentmarketplace/custom-plugin-sql --skill transactions

Review the files below or copy the command above to add this skill to your agents.

Files (6)

SKILL.md

962 B

---
name: transactions
description: Database transactions, ACID properties, and concurrency control
sasmp_version: "1.3.0"
bonded_agent: 01-sql-fundamentals
bond_type: PRIMARY_BOND
---

# Database Transactions Skill

## Overview
Understand and implement database transactions with ACID properties and proper concurrency control.

## Topics Covered

### ACID Properties
- Atomicity
- Consistency
- Isolation
- Durability
- Trade-offs

### Isolation Levels
- Read uncommitted
- Read committed
- Repeatable read
- Serializable
- Snapshot isolation

### Concurrency
- Locking mechanisms
- Deadlock handling
- Optimistic locking
- MVCC concepts
- Lock monitoring

### Transaction Patterns
- Savepoints
- Nested transactions
- Distributed transactions
- Two-phase commit
- Saga pattern

## Prerequisites
- SQL fundamentals
- Database concepts

## Learning Outcomes
- Implement ACID transactions
- Choose isolation levels
- Handle deadlocks
- Design concurrent systems

Overview

This skill teaches database transactions with a focus on ACID properties, isolation levels, and practical concurrency control. It covers patterns and tools for building reliable, consistent data operations in single-node and distributed systems. You will learn trade-offs and design choices that affect performance and correctness.

How this skill works

The skill inspects common transaction scenarios and explains how atomicity, consistency, isolation, and durability are achieved in SQL databases. It breaks down isolation levels, locking strategies, MVCC behavior, and failure-handling patterns such as two-phase commit and sagas. Concrete examples and patterns show how to apply savepoints, nested transactions, and deadlock recovery in application code.

When to use it

Implementing multi-step business operations that must be atomic
Choosing an appropriate isolation level to balance consistency and throughput
Designing distributed workflows that span multiple services or databases
Troubleshooting deadlocks, lock contention, or stale reads
Optimizing transactional throughput in high-concurrency environments

Best practices

Start with the weakest isolation that meets correctness, escalate only when necessary
Keep transactions short and move nonessential work outside the transaction boundary
Prefer optimistic concurrency for low-conflict workloads and MVCC-capable DBs
Use savepoints for partial rollbacks and sagas for long-running distributed processes
Monitor locks and transactions to detect contention and tune indexes or queries

Example use cases

Bank transfer: ensure atomic debit/credit across accounts with rollback on failure
Order processing: combine inventory, billing, and shipment updates with compensating actions
Concurrent counters: choose locking or optimistic updates to avoid lost updates
Schema migrations: run safe DDL in transactions or use phased deployments
Microservices: coordinate state changes across services using two-phase commit or saga pattern

FAQ

When should I use serializable isolation?

Use serializable when strict correctness is mandatory and anomalies cannot be tolerated; expect reduced concurrency and potential performance impact.

How do I handle deadlocks in production?

Detect and log deadlock incidents, ensure transactions are short, order resource acquisition consistently, and implement retry logic with backoff.

When are sagas preferable to two-phase commit?

Use sagas for long-running, loosely coupled distributed workflows where blocking coordinator-based protocols would be impractical; implement compensating transactions for failure recovery.