1

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.

2

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.

  • So setting isolation level as Serializable should take care of the point 2, I have mentioned? – Aman Gupta Jan 10 '17 at 6:38
  • I'm unsure of how to answer that question, likely yes, insofar as modification to the database goes. But, I was quite shocked last week to find out that the select(*) on the database doesn't predicate lock the whole table. This is likely out of the realm of beginner questions, so we can just pretend the answer is a simple "yes"? – Evan Carroll Jan 10 '17 at 6:45
  • 1
    I don't think people want to grab Serializable right away. There are usually better things to do than write Stored Procs and abundant transactions to begin with. And, when you do have to write them you usually don't need serializable. – Evan Carroll Jan 10 '17 at 6:47
  • If you want to geek out over an edge case, then check out this answer from a question I wrote this weekend. – Evan Carroll Jan 10 '17 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.