Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
To: Olivier Dony <odo+pggen@odoo.com>
References: <560AA479.4080807@odoo.com> <560DB385.2080904@BlueTreble.com>
 <560EB487.7010400@odoo.com>
CC: <pgsql-general@postgresql.org>, Kevin Grittner <kgrittn@ymail.com>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <560ED745.3050009@BlueTreble.com>
Date: Fri, 2 Oct 2015 14:13:09 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <560EB487.7010400@odoo.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Oct 2015 19:13:24.0173 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0679
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

On 10/2/15 11:44 AM, Olivier Dony wrote:
> On 10/02/2015 12:28 AM, Jim Nasby wrote:
>> On 9/29/15 9:47 AM, Olivier Dony wrote:
>>> My understanding of the KEY SHARE/NO KEY UPDATE locks introduced in 9.3
>>> was that they would avoid side-effects/blocking between transactions
>>> that are only linked via FK constraints, as long as the target PK was
>>> not touched. Isn't it the case here?
>>
>> Not quite. Any unique index that isn't partial and isn't a functional
>> index can satisfy a foreign key. That means that if you change a field
>> that is in ANY unique index that update becomes a FOR KEY UPDATE.
>
> Interesting, do you know if that is mentioned in the documentation
> somewhere? (I couldn't find it)

http://www.postgresql.org/docs/9.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK 
does say this:

"A foreign key must reference columns that either are a primary key or 
form a unique constraint."

So you can kind of infer it. It could probably be more explicitly 
mentioned somewhere though. Care to suggest a doc change?

>>> -- Setup tables
>>> CREATE TABLE users ( id serial PRIMARY KEY,
>>>                         name varchar,
>>>                         date timestamp );
>>> CREATE TABLE orders ( id serial PRIMARY KEY,
>>>                          name varchar,
>>>                          user_id int REFERENCES users (id) );
>>> INSERT INTO users (id, name) VALUES (1, 'foo');
>>> INSERT INTO orders (id, name) VALUES (1, 'order 1');
>>>
>>>
>>> -- Run 2 concurrent transactions: T1 and T2
>>>                T1                                T2
>>> |-----------------------------|----------------------------------|
>>>      BEGIN ISOLATION LEVEL
>>>            REPEATABLE READ;
>>>
>>>      UPDATE orders
>>>      SET name = 'order of foo',
>>>          user_id = 1
>>>      WHERE id = 1;
>>>
>>>                                        BEGIN ISOLATION LEVEL
>>>                                              REPEATABLE READ;
>>>
>>>                                        UPDATE users
>>>                                        SET date = now()
>>>                                        WHERE id = 1;
>>>
>>>                                        COMMIT;
>>>
>>>      UPDATE orders
>>>      SET name = 'order of foo (2)',
>>>          user_id = 1
>>>      WHERE id = 1;
>>>
>>> T1 fails with:
>>> ERROR:  could not serialize access due to concurrent update
>>> CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE
>>> "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
>>
>> This isn't a locking failure, it's a serialization failure. I'm not sure
>> why it's happening though... is there an index on date?
>
> I don't think so. I can reproduce the problem with the queries quoted
> above, and the only index that seems to be present is the PK (sorry for
> the wrapping):

I'm not sure. Perhaps Kevin Grittner (author of serializable patch) can 
shed some light.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general