I thought it would be interesting to see how other databases handle
this peculiar deadlock situation.
I didn't have access to any Oracle or Sybase databases, but for what
it's worth I've tested MySQL.
Results:
1. Process 1 successfully made its update and managed to commit.
2. Process 1 second update did not went straight through, but had to
wait for process 2 to attempt to commit.
3. Process 2 did not manage to commit, all its updates were discarded.
Demo of the test:
http://screencast.com/t/ZGJmMTcxN
/Joel
2010/8/25 Robert Haas <robertmhaas@gmail.com>:
> On Wed, Aug 25, 2010 at 10:02 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Wed, 2010-08-25 at 15:51 +0200, Markus Wanner wrote:
>>> Simon,
>>>
>>> On 08/25/2010 11:53 AM, Simon Riggs wrote:
>>> > ..we want to ensure that the PK value..
>>>
>>> ..or any other possibly referenced attributes?
>>
>> Don't think that's relevant.
>>
>> "referenced" meaning "by an RI constraint", which only ever refers to
>> PKs in other tables.
>
> That doesn't appear to be correct:
>
> rhaas=# create table p (a integer primary key, b integer not null,
> unique (b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
> index "p_pkey" for table "p"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index "p_b_key"
> for table "p"
> CREATE TABLE
> rhaas=# create table r (b integer not null references p (b));
> CREATE TABLE
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
--
Best regards,
Joel Jacobson
Glue Finance
E: jj@gluefinance.com
T: +46 70 360 38 01
Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden
Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden