473,398 Members | 2,812 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,398 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 3637
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...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

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.