Re: Concurrent testing PostgreSQL Vs MySQL

"Durai" <visolve_postgres@lycos.co.uk> writes:
> # cat testpgsql.php

> <?php
> $dbconn =3D pg_connect("host=3D172.16.1.158 port=3D5432 dbname=3Dtest user=
> =3Dpostgres");
> $result =3D pg_exec ($dbconn, "update table2 set C2=3DC2+1;");
> $result =3D pg_exec ($dbconn, "update table2 set C2=3DC2-1;");
> pg_close($dbconn);
> ?>
> #

It doesn't surprise me that concurrent execution of that script would
yield deadlocks in Postgres but not in MySQL.  The reason is that there
*isn't* any concurrent execution of that script going on in MySQL.
Each UPDATE command will (if I understand their behavior correctly) take
a table-level lock until it's done, thereby preventing any other UPDATE
from proceeding concurrently.  Postgres tries to do the locking at the
row level, and so can easily get into a state where transaction A has
updated row 1 and now wants to update row 2, whereas transaction B has
updated row 2 and now wants to update row 1 ... ie, deadlock.

You could "fix" this by taking a table-level lock ("LOCK TABLE table2")
before starting the updates, thereby dumbing Postgres down to MySQL's
level.  I don't see the point though, as this benchmark is completely
irrelevant to most real-world uses.  In the real world you more commonly
have different transactions independently updating different rows of a
table.  In that sort of scenario, MySQL loses badly because it cannot
process such updates concurrently, due to table-level locking.  Unless
your real application mostly does whole-table updates, you should
rewrite your benchmark to be more representative of what you really need
to do.

BTW, I think that when you use InnoDB tables, MySQL does use row-level
locks for updates, and so would likely show the same deadlock risk as
Postgres.

            regards, tom lane