
Understanding Postgres Concurrency with MVCC  - craigkerstiens
https://devcenter.heroku.com/articles/postgresql-concurrency
======
jaytaylor
It'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
In 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.

~~~
jordanthoms
InnoDB is the default now.

~~~
awj
Indeed it is. According to the manual this is true as of MySQL 5.5, so
sometime in 2010 it switched. Good to know.

------
btbuilder
It'd be nice if there was an example of deadlock scenarios.

~~~
bni
deadlock scenarios? didnt the article just explain how MVCC avoids them?

~~~
btbuilder
No. See (explicit locking is not required to create a deadlock scenario):

[http://www.postgresql.org/docs/9.1/static/explicit-
locking.h...](http://www.postgresql.org/docs/9.1/static/explicit-
locking.html#LOCKING-DEADLOCKS)

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
Thanks for the great explaination

