Brian May <[email protected]> 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
pgpUjOgDxiZQP.pgp
Description: PGP signature
_______________________________________________ luv-main mailing list [email protected] http://lists.luv.asn.au/listinfo/luv-main
