Session vs transaction

Feature	Session	Transaction
Starts when	Client connects	BEGIN
Ends when	Client disconnects	COMMIT or ROLLBACK
Explicit control	❌ No	✅ Yes
Can be nested	❌ No	✅ Yes (via savepoints)
Scope of temp objects	Session-wide	Depends on transaction
ACID guarantees	❌ No	✅ Yes

When client connects to Postgres the session automatically starts, there are no explicit commands in SQL for this. How to check open sessions:

SELECT * FROM pg_stat_activity;

For example the session will be started for this command

psql -h localhost -p 5437 -U postgres -d postgres

Explicit Transaction Control

BEGIN; -- Start transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If everything looks good
COMMIT; -- Save changes

-- Or, if something went wrong
ROLLBACK; -- Discard changes

📌 This is explicit transaction control — you decide when the transaction starts and ends.

Savepoints (Nested Transactions)

You can also use savepoints to manage parts of a transaction:

BEGIN;
INSERT INTO logs (message) VALUES ('Start');
SAVEPOINT before_error;

-- Simulate error
INSERT INTO logs (message) VALUES ('Error occurred');

-- Rollback to savepoint
ROLLBACK TO before_error;
COMMIT;

This gives you fine-grained control within a single transaction.

Multiple Transactions in One Session

A PostgreSQL session can run many transactions, one after another.

Each transaction is isolated from the next, unless you explicitly share state using temporary tables, GUCs, or other session-level constructs.

---

🧪 Example: Multiple Transactions in One Session

-- Transaction 1
BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
COMMIT;

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- Transaction 3
BEGIN;
DELETE FROM accounts WHERE id = 1;
ROLLBACK; -- Changes discarded

✅ Each transaction is explicitly started with BEGIN and ended with COMMIT or ROLLBACK.

After each transaction, the session is ready to start a new one.

Best Practice

• Use session-scoped features (like temporary tables or SET LOCAL) only if you're using session pooling (e.g., HikariCP or pgBouncer in session mode).

• Prefer transaction pooling when possible for better scalability and stateless operations.

• Always assume transactions are isolated, but sessions can retain state.

Breakdown: Spring Boot + Hibernate + PostgreSQL

1. Database Session (Connection)

• A session in PostgreSQL is a TCP connection from the client (your app) to the database.

• In Spring Boot, connections are usually pooled using HikariCP, Tomcat JDBC, or pgBouncer.

• So, sessions are reused — not created for every transaction.

2. Hibernate Session (EntityManager)

• Hibernate’s Session or EntityManager is a wrapper over the JDBC connection.

• It can be bound to a transaction and reused within the same request or method.

• It is not the same as a PostgreSQL session.

3. JPA Transaction

• A JPA transaction corresponds to a SQL transaction (BEGIN, COMMIT, ROLLBACK).

• Each @Transactional method in Spring opens a new transaction on the same session (connection) if it’s already open.

• So, multiple transactions can run on the same session.

---

📌 Example: Spring Boot Code

@Service
public class AccountService {

    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
        Account from = entityManager.find(Account.class, fromId);
        Account to = entityManager.find(Account.class, toId);

        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));

        entityManager.merge(from);
        entityManager.merge(to);
    }
}

What happens under the hood?

1. Spring checks if there's an existing transaction:

   • If not, it starts a new transaction (BEGIN).

2. It uses a connection from the connection pool (e.g., HikariCP).

3. The transaction ends with COMMIT (or ROLLBACK on error).

4. The connection is returned to the pool, but not closed — it can be reused.

---

🧪 Real-World SQL Flow

Assuming two calls to transferMoney() in the same thread:

-- Connection from pool (session PID = 12345)
BEGIN;
UPDATE accounts SET balance = ... WHERE id = 1;
UPDATE accounts SET balance = ... WHERE id = 2;
COMMIT;

BEGIN;
UPDATE accounts SET balance = ... WHERE id = 3;
UPDATE accounts SET balance = ... WHERE id = 4;
COMMIT;

✅ Both transactions run on the same session (PID 12345).

---

🧠 Connection Pooling Behavior

Pooling Layer	Behavior
HikariCP	Reuses connections (sessions) across transactions in the same thread
pgBouncer (transaction mode)	May not reuse the same session for multiple transactions
pgBouncer (session mode)	Reuses the same session for the entire connection lifetime

⚠️ In pgBouncer transaction mode, temporary tables or session variables won't work across transactions — because each transaction may run on a different session.