mysql transaction theory
Hello, If I have two transactions in two different sessions that execute a "INSERT INTO ..." of the same primary key into the one table, one session will wait for the other session to complete before being allowed to resume. Something like this obviously has to happen, Mysql has no idea of the first transaction is going to complete with "rollback" or "commit" and as such can't tell if the second INSERT should fail or not (conflicting primary key). It didn't match my limited understanding of how SQL transactions work however, and I would be interested to more about why the second process is blocked, and what the scope is for this block, etc. Anyone have any good references? The only thing I can find about transactions is that they are atomic, but nothing about transactions causing other processes to block. Is this the same for other non-mysql databases? Thanks -- Brian May <brian@microcomaustralia.com.au>
Brian May <brian@microcomaustralia.com.au> wrote:
The only thing I can find about transactions is that they are atomic, but nothing about transactions causing other processes to block.
Is this the same for other non-mysql databases?
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).
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. Now if only I could find the equivalent documentation for mysql :-) -- Brian May <brian@microcomaustralia.com.au>
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
Brian May <brian@microcomaustralia.com.au> writes:
If I have two transactions in two different sessions that execute a "INSERT INTO ..." of the same primary key into the one table, one session will wait for the other session to complete before being allowed to resume.
Something like this obviously has to happen, Mysql has no idea of the first transaction is going to complete with "rollback" or "commit" and as such can't tell if the second INSERT should fail or not (conflicting primary key).
What's actually going on here is that there's a lock on the range of keys held by the txn doing the INSERT that the transaction that wants to do the INSERT is waiting on. This is pessimistic concurrency control. Another approach is optimistic concurrency control where both INSERTS will succeed but one of the transactions will be rolled back when it tries to COMMIT. Sometimes, a system can have a mix of the two - for example if you use the Galera system with MySQL you get pessimistic concurrency controll local to each node and optimistic concurrency control between nodes.
It didn't match my limited understanding of how SQL transactions work however, and I would be interested to more about why the second process is blocked, and what the scope is for this block, etc. Anyone have any good references?
The only thing I can find about transactions is that they are atomic, but nothing about transactions causing other processes to block.
You'll want to look up innodb gap locks and how they work.
Is this the same for other non-mysql databases?
The answer is "it depends". Sometimes it'll be really similar, other times it'll be much different - depending on the trade-offs of the RDBMS. -- Stewart Smith
participants (3)
-
Brian May -
Jason White -
Stewart Smith