
Brian May <brian@microcomaustralia.com.au> writes:
On 22 November 2013 17:06, Jason White <jason@jasonjgw.net> wrote:
I don't know much about database systems, so this might not help, but the PostGRESQL documentation appears relevant: http://www.postgresql.org/docs/9.3/static/mvcc-intro.html (be sure to read the sections that follow the introduction for details).
Ok, thanks for this, very interesting reading. Looks like postgresql has 4 different levels, depending on application requirements.
This is transaction isolation: READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ and SERIALIZABLE which are different to why you'll get various locks when trying conflicting INSERTs. The I in ACID is for Isolation, and this is basically that transactions can be isolated from each other - only really possible if they get a consistent view of the world, which is what all of those isolation levels are. e.g. if in READ_UNCOMMITTED isolation level, the second transaction could see the not-yet-committed row from the first transaction, while in any other isolation mode it cannot. The default is generally REPEATABLE_READ (at least for MySQL/InnoDB) and changing this is... well... brave :)
Now if only I could find the equivalent documentation for mysql :-)
Start here: https://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html -- Stewart Smith