473,287 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Deadlock. Referential Integrity checks select for update?

Hi

First a simple question ...

I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into "access_log", the referential integrity
triggers generate these queries:

SELECT 1 FROM ONLY "public"."application_type" x
WHERE "app_id" = $1 FOR UPDATE OF x

SELECT 1 FROM ONLY "public"."app_user" x
WHERE "app_user_id" = $1 FOR UPDATE OF x

I don't understand why these two queries include "FOR UPDATE". Can
anyone explain that?
That question may in fact be a red herring as far as my real problem
goes, so I'll describe that too ...

I have a web application in which transactions die with database
deadlocks under load. I have isolated the problem to when two
different types of transaction occur in parallel:

Transaction A:
create a new record in "session" and get its ID then
create a new record in "access_log" and get its ID

Transaction B:
retrieve an existing record from "session" using its ID then
create a new record in "access_log" and get its ID

In practice, the initial select from session in transaction B doesn't
affect the deadlock, so I've omitted it here.

The theoretical parallel sequence of queries is:

A1 insert_session B1 insert_access_log
A2 get_session_id B2 get_access_log_id
A3 insert_access_log B3 commit
A4 get_access_log_id
A5 commit

The postgresql log show this is the sequence that causes deadlock:

A1 insert_session
riA select from app_user for update
B1 insert_access_log
riB select from app_type for update
riB select from app_user for update
<< Process B blocked >>
A2 get_session_id
A3 insert_access_log
riA select from app_type for update
<< DEADLOCK >>
<< Process A killed >>

"riA" and "riB" refer to system-generated statements which check
referential integrity.

Neither transaction A nor transaction B ever updates either the
application_type or app_user tables and the deadlock only seems
to happen as a result of the referential integrity checks locking
the parent tables for update. So it seems that even if I'm careful
to always do my updates in a consistent order to avoid deadlock,
the referential integrity constraints can cause deadlock themselves.
Here's the snippet from the postgresql log:

15:37:15 [20480] LOG: statement: select getdatabaseencoding()
15:37:15 [20480] LOG: statement: begin
15:37:15 [20481] LOG: statement: select getdatabaseencoding()
15:37:15 [20481] LOG: statement: begin
15:37:40 [20480] LOG: statement: INSERT INTO session (app_user_id,
session_data) VALUES ('1', '')
15:37:40 [20480] LOG: statement: SELECT 1 FROM ONLY "public"."app_user"
x WHERE "app_user_id" = $1 FOR UPDATE OF x

15:37:51 [20481] LOG: statement:
INSERT INTO access_log (app_id, app_user_id, log_data)
VALUES ('1', '1', 'Log data here')

15:37:51 [20481] LOG: statement: SELECT 1 FROM ONLY
"public"."application_type" x WHERE "app_id" = $1 FOR UPDATE OF x
15:37:51 [20481] LOG: statement: SELECT 1 FROM ONLY "public"."app_user"
x WHERE "app_user_id" = $1 FOR UPDATE OF x

15:38:04 [20480] LOG: statement: SELECT currval('seq_session_id')

15:38:17 [20480] LOG: statement:
INSERT INTO access_log (app_id, app_user_id, log_data)
VALUES ('1', '1', 'Log data here')

15:38:17 [20480] LOG: statement: SELECT 1 FROM ONLY
"public"."application_type" x WHERE "app_id" = $1 FOR UPDATE OF x

15:38:20 [20480] ERROR: deadlock detected
DETAIL: Process 20480 waits for ShareLock on transaction 74278; blocked
by process 20481.
Process 20481 waits for ShareLock on transaction 74275; blocked by
process 20480.
15:38:20 [20480] LOG: statement: rollback


And here's a short script to create the database:

#!/bin/sh

DBNAME=deadpool

if psql -l | grep -q $DBNAME
then
dropdb $DBNAME || exit
fi

createdb $DBNAME

psql -d $DBNAME <<EOF

create sequence seq_access_log_id;

create sequence seq_session_id;

create table app_user (
app_user_id INT4 not null,
login_name TEXT not null,
constraint PK_APP_USER primary key (app_user_id)
);

create table application_type (
app_id INT4 not null,
app_desc TEXT not null,
constraint PK_APPLICATION_TYPE primary key (app_id)
);

create table session (
session_id INT8 not null default
nextval('seq_session_id'),
app_user_id INT4 not null,
session_data TEXT not null,
constraint PK_SESSION primary key (session_id),
constraint FK_SESS_REFERENCE_APP_ foreign key (app_user_id)
references app_user (app_user_id)
deferrable
);

create table access_log (
access_log_id INT8 not null default
nextval('seq_access_log_id'),
app_id INT4 not null,
app_user_id INT4 not null,
log_data TEXT null,
constraint PK_ACCESS_LOG primary key (access_log_id),
constraint FK_ACCE_REFERENCE_APPL foreign key (app_id)
references application_type (app_id)
deferrable,
constraint FK_ACCE_REFERENCE_APP_ foreign key (app_user_id)
references app_user (app_user_id)
deferrable
);

create index access_log_app_type_fk on access_log (
app_id
);

create index access_log_app_user_id on access_log (
app_user_id
);

insert into application_type values (1, 'Public Web App');

insert into app_user values (1, 'anonymous');

EOF
I also have a Perl script that steps through the two transactions in the
appropriate order and causes the deadlock every time.
Any assistance anyone can offer would be appreciated.

Regards
Grant

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
1 3626
Grant McLean <gr***@catalyst.net.nz> writes:
I don't understand why these two queries include "FOR UPDATE".


The point is to lock the referenced rows so they cannot be deleted
before the referencing transaction commits. FOR UPDATE is an overly
strong lock, but we do not presently have any weaker lock that will
serve. You can find lots more about this issue in the archives...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: yacht | last post by:
Is it bug? CREATE TABLE base ( base INT PRIMARY KEY ); CREATE TABLE child ( val INT ) INHERITS ( base ); CREATE TABLE ref ( atr INT , base INT REFERENCES base ON UPDATE cascade ON DELETE...
4
by: Mike Dole | last post by:
I might have taken a wrong turn trying to do an update on a database the way I did but maybe somebody can help me out cause I'm stuck.. I have to update 12 tables in a database in a client...
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
7
by: Jimmie H. Apsey | last post by:
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential...
5
by: Geisler, Jim | last post by:
So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. Are there any recommended methods or utilities for checking referential integrity in a...
80
by: Andrew R | last post by:
Hi I'm creating a series of forms, each with with around 15-20 text boxes. The text boxes will show data from tables, but are unbound to make them more flexible. I want the form to be used...
3
by: Wayne | last post by:
I've inadvertently placed this post in another similar newgroup, and I apologise if you get it twice. I'm building a database that consists of frontend and backend. Some of the lookup tables...
0
by: desmondgreen | last post by:
We have a problem with transaction management/concurrency when using ADO to update a database on a SQL 2005 database. We have a test application, to isolate and demonstrate the problem, where a VB6...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.