Database.Middle.When we start transaction, what is locked, table or row ?

The answer depends on the type of SQL statements you run, your database engine, and the transaction isolation level.

Here’s a clear breakdown:

🔐 When You Start a Transaction

BEGIN;  -- or START TRANSACTION

At this point, nothing is locked yet.

❗ Locks are acquired only when you access data (e.g. with SELECT, UPDATE, DELETE, etc.)

---

🔍 What Gets Locked?

1. Row-level Locks (Most Common)

• For most UPDATE, DELETE, SELECT ... FOR UPDATE queries
• Only the specific rows that match your query are locked

✅ Efficient and allows high concurrency

-- Locks only the row where id = 1
UPDATE users SET name = 'Alice' WHERE id = 1;

— Locks only the row where id = 1
UPDATE users SET name = ‘Alice’ WHERE id = 1;

2. Table-level Locks

• Acquired explicitly (LOCK TABLE) or in some implicit cases:
  • DDL operations (ALTER TABLE, DROP)
  • Full table scans in some MyISAM tables (MySQL)
  • Sometimes used for performance optimization

LOCK TABLE users IN EXCLUSIVE MODE;  -- PostgreSQL

❗ Prevents any other access (read/write) depending on lock type

3. Page or Predicate Locks (Advanced/rare)

• Used in some isolation levels like REPEATABLE READ or SERIALIZABLE
• May lock ranges or “gaps” between rows (to prevent phantom reads)

-- In SERIALIZABLE mode, PostgreSQL might lock ranges
SELECT * FROM users WHERE age > 30;

🔄 Isolation Level Influence

Isolation Level	Lock Behavior
READ COMMITTED	Only rows touched are locked
REPEATABLE READ	Locks rows and prevents non-repeatable reads
SERIALIZABLE	May lock row ranges to prevent phantoms

🧠 Summary

Action	Locks Acquired
BEGIN	None (yet)
UPDATE, DELETE	Row-level locks
SELECT FOR UPDATE	Row-level locks (read + lock)
LOCK TABLE	Table-level lock
DDL (ALTER, DROP)	Implicit table lock