CODEWITHSUNDEEP
databasepostgresqlacid
Mansour.M
Mansour.M

Reputation: 510

How is an update query on a locked database resolved in postgresql?

Using postgresql, if a user wants to add new data or update existing data in the database while it is locked, how is his transaction resolved? Lets consider this scenario and please correct me if my understanding is wrong at any point:

1. User 1 wants to batch update some records in the database.
2. The transaction from user 1 locks the database until all the updates are pushed.
3. User 2 wants to update something in the database, or insert some new data to the database while it is locked.
4. Based on what MVCC denotes, user 2 is shown the pre-lock version of the database.
5. User 2 inserts or updates the data.
6. User one finishes pushing its transaction and releases the database.
7. There are two versions of database now, the data is resolved.

How does the issue in step 7 get resolved? I read somewhere that it will take the data with the latest global time stamp. But how can I be sure that is the data it should keep? If the data from user 2 has priority over user 1, but the transaction from user 2 finished before user 1, how would this priority be resolved? Thank you for your help.

Upvotes: 0

Views: 1136

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248075

You cannot lock the database in PostgreSQL, but you can lock a table exclusively. This is something you should never do, because it is not necessary and hurts concurrency and system maintenance processes (autovacuum).

Every row you modify or delete will be automatically locked for the duration of the transaction. This does not affect the modification of other rows by concurrent sessions.

If a transaction tries to modify a row that is already locked by another transaction, the second transaction is blocked and has to wait until the first transaction is done. This way the scenario you describe is avoided.

Upvotes: 1

Related Questions