4 ms·

Row-level locks are really sweet in case you have to handle multiple row/tables update/insert in the same transaction, in order to keep the db consistent even u

by agopaul 13y ago

Row-level locks are really sweet in case you have to handle multiple row/tables update/insert in the same transaction, in order to keep the db consistent even under sustained concurrency. But, dead locks really strike you if you don't use them carefully. I wonder if dead locks are easyer to prevent in Postgres

babuskov 13y ago
InnoDB and MVCC in Postgres are almost indentical, so it's all up to the programmer. Well, except for one difference. InnoDB will allow you refer to tables not controlled by the InnoDB table handler, whereas PG does not have such potential problem. If you keep it all-InnoDB, there should be no difference.
- kozlovsky 13y ago
  MVCC in InnoDB and PostgreSQL are totally different. It is PostgreSQL and Oracle MVCC who have very similar, although not identical isolation behaviour (albeit very different implementation), but InnoDB MVCC stands apart from them. At the beginning, I should point that the default isolation level in PostgreSQL and Oracle is READ COMMITTED, whereas MySQL uses REPEATABLE READ isolation level as a default. This means that in MySQL by default all normal (not FOR UPDATE) SELECT queries see database snapshot at the time when transaction started. In PostgreSQL and Oracle all SELECT queries by default see database snapshot at the time when the query (and not the transaction) started. However, SELECT FOR UPDATE in MySQL sees database at the moment the query started, even with REPEATABLE READ isolation level. That is, it is possible that during the same transaction on default isolation level in MySQL SELECT and SELECT FOR UPDATE see totally different rows. In Oracle and PostgreSQL, SELECT and SELECT FOR UPDATE always see the same rows (at the moment of query start in READ COMMITTED mode, or at the moment of transaction start when in SERIALIZABLE mode). In other words, in Oracle and PostgreSQL SELECTs and UPDATEs see database at the same time in past, whereas in MySQL SELECTs see consistent snapshot at past, whereas SELECT FOR UPDATE and UPDATE see current data. Also, as MySQL uses locks more aggressively, some "write skew" problems which are possible in PostgreSQL and Oracle, are not possible in MySQL. I don't know what kind of MVCC is "better", but IMHO Oracle and PostgreSQL MVCC is much easier to understand than MySQL one. The difference between Oracle and PostgreSQL MVCC is the following: in READ COMMITTED mode, when long UPDATE query encounter some rows updated by concurrent transaction, it is blocked until concurrent transaction COMMIT or ROLLBACK. After ROLLBACK of concurrent transaction the query will continues execution, and this behavior is the same in Oracle and PostgreSQL. If concurrent transaction is committed, then PostgreSQL re-evaluates previously blocked row to determine whether it still matches the UPDATE criteria. But PostgreSQL does not re-check previously read rows which did not satisfy UPDATE criteria, whereas is is possible that these rows were also modified by concurrent transaction and now satisfy UPDATE criteria and should be updated. Oracle behaves in a different way: If long UPDATE query encounters some rows updated by concurrent transaction, and these rows no longer satisfy the UPDATE criteria, the query restarts and sees new database snapshot at the current time (remainder - I'm speaking about the default READ COMMITTED mode). This way Oracle doesn't miss rows which can be missed by PostgreSQL, but long UPDATE can potentially be restarted many times (that is, the query starvation is possible, although not likely). http://asktom.oracle.com/pls/asktom/f?p=100:11%3a%3a%3a%3a%3aP11_QUESTION_ID:11504247549852 http://asktom.oracle.com/pls/asktom/f?p=100:11%3a%3a%3a%3a%3...
  - Roboprog 13y ago
    Yikes. Thanks, though.
Roboprog 13y ago
MVCC is the 21st century database hotness :-) Rather than locking rows or tables (etc), you keep versions on rows, and allow isolated changes to versioned "duplicates" of the rows in transactions. There is a process that relatively quickly checks for conflicts during a commit -- is there a "gap" in the version numbers of rows or some such thing. If inconsistency would result, the first commit (already) wins, and the second gets rolled back. Otherwise, the replacement (or new) rows become the new "master" version. The commit check can become a bottleneck, but it shouldn't deadlock. Almost forgot to add: PostgreSQL has been using MVCC for quite some time. My view of MySQL was discolored pretty badly when I needed an SQL database back in 2001 to prototype some stuff from home, and discovered how "gappy" MySQL was at the time: rollback did nothing; referential integrity not enforced; lack of transaction isolation. It felt like XBase hidden under SQL syntax (something to which I did not want to regress). OTOH, MySQL ran on Windows, and PostgreSQL didn't (at the time), so I guess that was "the win" for many, alas. Both DBs have become much more like each other in the intervening decade, though, which is good news.
- agopaul 13y ago
  This is interesting. I think developers should be more aware of how the DBMS works under the hood. Edit: In my case, I still use row-level lock when implementing the state machine pattern on a model. It enforces the application-level-consistency of the model state, preventing double updates in case of high concurrency, in which the second (concurrent) update would break the application logic.
- mjb 13y ago
  You are confusing two concepts: locking used by the implementation to provide isolation and atomicity, and locking used by the application to provide consistency. Consistency is a separate concern from Isolation and Atomicity. With the default isolation level (repeatable read) it is frequently necessary to use row-level locks to ensure consistency with concurrent transactions. This is an application-level concern, and an interactions with the isolation level rather than an interaction with the implementation. Short of SSI (serializability), hazards exist which can have counterintutive impacts on data consistency. The implementation's use of MVCC is irrelevant. Locking is also frequently used in applications to help ensure external consistency, but that's a pattern with real and significant pitfalls that should be avoided.

by agopaul 13y ago

babuskov 13y ago

InnoDB and MVCC in Postgres are almost indentical, so it's all up to the programmer. Well, except for one difference. InnoDB will allow you refer to tables not controlled by the InnoDB table handler, whereas PG does not have such potential problem. If you keep it all-InnoDB, there should be no difference.

kozlovsky 13y ago
MVCC in InnoDB and PostgreSQL are totally different. It is PostgreSQL and Oracle MVCC who have very similar, although not identical isolation behaviour (albeit very different implementation), but InnoDB MVCC stands apart from them. At the beginning, I should point that the default isolation level in PostgreSQL and Oracle is READ COMMITTED, whereas MySQL uses REPEATABLE READ isolation level as a default. This means that in MySQL by default all normal (not FOR UPDATE) SELECT queries see database snapshot at the time when transaction started. In PostgreSQL and Oracle all SELECT queries by default see database snapshot at the time when the query (and not the transaction) started. However, SELECT FOR UPDATE in MySQL sees database at the moment the query started, even with REPEATABLE READ isolation level. That is, it is possible that during the same transaction on default isolation level in MySQL SELECT and SELECT FOR UPDATE see totally different rows. In Oracle and PostgreSQL, SELECT and SELECT FOR UPDATE always see the same rows (at the moment of query start in READ COMMITTED mode, or at the moment of transaction start when in SERIALIZABLE mode). In other words, in Oracle and PostgreSQL SELECTs and UPDATEs see database at the same time in past, whereas in MySQL SELECTs see consistent snapshot at past, whereas SELECT FOR UPDATE and UPDATE see current data. Also, as MySQL uses locks more aggressively, some "write skew" problems which are possible in PostgreSQL and Oracle, are not possible in MySQL. I don't know what kind of MVCC is "better", but IMHO Oracle and PostgreSQL MVCC is much easier to understand than MySQL one. The difference between Oracle and PostgreSQL MVCC is the following: in READ COMMITTED mode, when long UPDATE query encounter some rows updated by concurrent transaction, it is blocked until concurrent transaction COMMIT or ROLLBACK. After ROLLBACK of concurrent transaction the query will continues execution, and this behavior is the same in Oracle and PostgreSQL. If concurrent transaction is committed, then PostgreSQL re-evaluates previously blocked row to determine whether it still matches the UPDATE criteria. But PostgreSQL does not re-check previously read rows which did not satisfy UPDATE criteria, whereas is is possible that these rows were also modified by concurrent transaction and now satisfy UPDATE criteria and should be updated. Oracle behaves in a different way: If long UPDATE query encounters some rows updated by concurrent transaction, and these rows no longer satisfy the UPDATE criteria, the query restarts and sees new database snapshot at the current time (remainder - I'm speaking about the default READ COMMITTED mode). This way Oracle doesn't miss rows which can be missed by PostgreSQL, but long UPDATE can potentially be restarted many times (that is, the query starvation is possible, although not likely). http://asktom.oracle.com/pls/asktom/f?p=100:11%3a%3a%3a%3a%3aP11_QUESTION_ID:11504247549852 http://asktom.oracle.com/pls/asktom/f?p=100:11%3a%3a%3a%3a%3...
- Roboprog 13y ago
  Yikes. Thanks, though.

Roboprog 13y ago

MVCC is the 21st century database hotness :-) Rather than locking rows or tables (etc), you keep versions on rows, and allow isolated changes to versioned "duplicates" of the rows in transactions. There is a process that relatively quickly checks for conflicts during a commit -- is there a "gap" in the version numbers of rows or some such thing. If inconsistency would result, the first commit (already) wins, and the second gets rolled back. Otherwise, the replacement (or new) rows become the new "master" version. The commit check can become a bottleneck, but it shouldn't deadlock. Almost forgot to add: PostgreSQL has been using MVCC for quite some time. My view of MySQL was discolored pretty badly when I needed an SQL database back in 2001 to prototype some stuff from home, and discovered how "gappy" MySQL was at the time: rollback did nothing; referential integrity not enforced; lack of transaction isolation. It felt like XBase hidden under SQL syntax (something to which I did not want to regress). OTOH, MySQL ran on Windows, and PostgreSQL didn't (at the time), so I guess that was "the win" for many, alas. Both DBs have become much more like each other in the intervening decade, though, which is good news.

agopaul 13y ago
This is interesting. I think developers should be more aware of how the DBMS works under the hood. Edit: In my case, I still use row-level lock when implementing the state machine pattern on a model. It enforces the application-level-consistency of the model state, preventing double updates in case of high concurrency, in which the second (concurrent) update would break the application logic.
mjb 13y ago
You are confusing two concepts: locking used by the implementation to provide isolation and atomicity, and locking used by the application to provide consistency. Consistency is a separate concern from Isolation and Atomicity. With the default isolation level (repeatable read) it is frequently necessary to use row-level locks to ensure consistency with concurrent transactions. This is an application-level concern, and an interactions with the isolation level rather than an interaction with the implementation. Short of SSI (serializability), hazards exist which can have counterintutive impacts on data consistency. The implementation's use of MVCC is irrelevant. Locking is also frequently used in applications to help ensure external consistency, but that's a pattern with real and significant pitfalls that should be avoided.