PostgreSQL – Explicit Lock with Transactions

lockingpostgresqltransaction

I am a beginner in SQL world and have some doubts regarding transaction/locking:

In postgres, do transaction also ensures locking of rows(preferable)/tables until it is completed?
Also, if there are two transactions through different queries, will second one wait for the first one to complete i.e. will it be serial or concurrent?

I think both points infers same but I am not sure so posting both.

Best Answer

In postgres, do transaction also ensures locking of rows(preferable)/tables until it is completed?

No, transactions don't ensure locking. Transactions ensure only that whatever is transacting is done atomically. That is to say, that it either all succeeds, or it all fails. How it does what it does, and what it is doing it to is totally controlled by you.

For instance, PostgreSQL currently supports three isolation levels,

Read committed
Repeatable read
Serializable

These three isolation levels either see and operate can see the data directly, or a snapshot of the data at the time transaction starts.

Only in the last mode serializable is any attempt to be made that the transaction commits in a serialized fashion. Whether or not enough of an attempt is made is another question.

All locking in PostgreSQL can be done explicitly or implicitly (the DDL command know how to lock efficiently to get the job done). Usually, explicit locks are shunned. But in same cases, including under serializable, it's the only way to be sure data has not been added since the transaction started.

Related Solutions

Postgresql – Inconsistency in repeatable read

Here is my reading of that section. I will admit it is confusing.

Suppose I have two tables:

CREATE TABLE batch (
   id serial not null unique,
   control_code text primary key,
   date_posted date not null default now()
);

CREATE TABLE details (
   batch_id int not null references batch(id),
   description text,
   primary key(batch_id, description)
);

Now, suppose we insert batch and details records in different transactions. Session 1 inserts a batch and starts to insert details but before it finishes, session 2 starts up. Session 2 gets to see the batch heading info, but does not wait on the commit on details to inform the user that no records are found. Now if your batch and details are entirely in the same transaction then this never is a problem.

this would differ from serializable where you would expect to wait for the previous insert to complete and commit or rollback before determining whether to notify the user that no rows were found.

PostgreSQL 9.3 – Locking Issues Under Transaction

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks.

RELEASE SAVEPOINT after you're done with a step.

You might also want to consider batching the work into smaller chunks, as the:

SAVEPOINT
Try it
ROLLBACK TO SAVEPOINT if it fails, RELEASE SAVEPOINT if it succeeds

pattern works, but has some performance costs that scale with number of savepoints in a transaction.

This applies to PL/PgSQL BEGIN ... EXCEPTION blocks too.

Best Answer

Related Solutions

Postgresql – Inconsistency in repeatable read

PostgreSQL 9.3 – Locking Issues Under Transaction

Related Question