5 ms·
Understanding Postgres Concurrency with MVCC
- btbuilder 14y agoIt'd be nice if there was an example of deadlock scenarios.
- bni 14y agodeadlock scenarios? didnt the article just explain how MVCC avoids them?
- btbuilder 14y agoNo. See (explicit locking is not required to create a deadlock scenario): http://www.postgresql.org/docs/9.1/static/explicit-locking.html#LOCKING-DEADLOCKS http://www.postgresql.org/docs/9.1/static/explicit-locking.h... Here's a (badly formatted) example: CREATE TABLE testing (value int, id serial); insert into testing values (1); insert into testing values (1); testing=# begin; BEGIN testing=# update testing set value = 2 where id = 1; UPDATE 1 (now in another session) BEGIN testing=# update testing set value = 2 where id = 2; UPDATE 1 (go back to original session) testing=# update testing set value = 2 where id = 2; UPDATE 1 (go back to second session) testing=# update testing set value = 2 where id = 1; ERROR: deadlock detected DETAIL: Process 11571 waits for ShareLock on transaction 2746675; blocked by process 11495. Process 11495 waits for ShareLock on transaction 2746676; blocked by process 11571. HINT: See server log for query details.
- bni 14y agoThanks for the great explaination
- jaytaylor 14y agoIt's funny how the author seems to have gone out of their way to avoid mentioning MySQL in the list of database systems which utilize MVCC. Just saying.
- awj 14y agoIn its default configuration, MySQL doesn't support MVCC. It's the InnoDB storage engine that does, and you have to (know you need to) go out of your way to get it. Just saying.
- jaytaylor 14y agoThat's a good point. I still find the omission amusing.
- awj 14y agoYeah, it is. Even if you add the caveat of "MySQL supports it (with the InnoDB storage engine)", it seems ridiculous to omit MySQL from the list.
- jordanthoms 14y agoInnoDB is the default now.
- ftwinnovations 14y agoBut, were you just saying?
- awj 14y agoIndeed it is. According to the manual this is true as of MySQL 5.5, so sometime in 2010 it switched. Good to know.
- fdr 14y agoI do happen to know the author. He worked in, as I understand it, an MSSQL environment beforehand, but interestingly he didn't mention that either. My guess: his thinking was MVCC-implementing databases that could fit the category: "an archetypal proprietary RDBMS, an archetypal embedded database, an archetypal document database/'NoSQL'" for some point in time. The people going so far to even hit that page already know about client-server, open source, OLTP SQL databases and maybe even some of their shared techniques. In this list, InnoDB technically should be in the 'embedded database' category. Point being: I think it's awfully assumptive to presume a conspiracy against MySQL, old feuds aside. The world is just very large and this tries to draw a shape around a bigger part of it.