pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Deadlock problem
2+ messages / 2 participants
[nested] [flat]

* Deadlock problem
@ 2003-04-18 09:06  Tzvetan Tzankov <ce@noxis.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Tzvetan Tzankov @ 2003-04-18 09:06 UTC (permalink / raw)
  To: pgsql-sql

Hallo,

I have the following problem ( I hope somebody will have the nerves to 
read it )

here is the DB schema (sorry it is only part of the schema, but it's 
still large, I've tried to isolate only the things I think are important 
in the case)

CREATE TABLE viewables (
	advert int4 not null PRIMARY KEY REFERENCES advert on delete cascade,
	views int4 not null,
	tviews int4 not null,
	clicks int4 not null default 0,
	paused bool not null default 'f',
	payed bool not null default 'f',
	start date not null
);

CREATE TABLE periodables (
	advert int4 not null PRIMARY KEY REFERENCES advert on delete cascade,
	finish date not null,
	tviews int4 not null default 0,
	clicks int4 not null default 0,
	paused bool not null default 'f',
	payed bool not null default 'f',
	start date not null
);

CREATE OR REPLACE FUNCTION check_parent(text) RETURNS name
AS '
	SELECT p.relname FROM pg_class AS p, pg_class AS c, pg_inherits AS r 
WHERE c.relname = $1 AND r.inhrelid = c.relfilenode AND p.relfilenode = 
inhparent;
'
LANGUAGE 'sql' IMMUTABLE;

CREATE TABLE bazar (
	bazar int4[] not null
) INHERITS (periodables);
CREATE INDEX bazar_advert ON bazar(advert);
CREATE INDEX bazar_rdtree_idx ON bazar using gist (bazar gist__int_ops);

CREATE TABLE dir (
	dir int4[] not null,
	city int4[] not null
) INHERITS (viewables);
CREATE INDEX dir_advert ON dir(advert);
CREATE INDEX dir_rdtree_idx ON dir USING gist ( dir gist__int_ops);

CREATE TABLE magazine (
	magazine int4[] not null
) INHERITS (viewables);
CREATE INDEX magazine_advert ON magazine(advert);
CREATE INDEX magazine_rdtree_idx ON magazine USING gist ( magazine 
gist__int_ops);

CREATE OR REPLACE FUNCTION ch_payed_viewable() RETURNS trigger
AS '
begin
	if tg_op = ''UPDATE'' then
		if old.payed = ''t'' then
			new.views = old.views;
			new.payed = ''t'';
		else
			if new.payed = ''t'' then
				new.paused = ''f'';
			else
				new.paused = ''t'';
			end if;
			new.clicks = 0;
			new.tviews = new.views;
		end if;
	else
		new.paused = ''t'';
		new.clicks = 0;
		new.tviews = new.views;
	end if;
	return new;
end;
'
LANGUAGE 'plpgsql' STRICT;

CREATE TRIGGER biu_dirpayed BEFORE UPDATE OR INSERT ON dir
	FOR EACH ROW EXECUTE PROCEDURE ch_payed_viewable();
CREATE TRIGGER biu_magazinepayed BEFORE UPDATE OR INSERT ON magazine
	FOR EACH ROW EXECUTE PROCEDURE ch_payed_viewable();

CREATE OR REPLACE FUNCTION ch_payed_periodable() RETURNS trigger
AS '
begin
	if tg_op = ''UPDATE'' then
		if old.payed = ''t'' then
			new.finish = old.finish;
			new.payed = ''t'';
		else
			if new.payed = ''t'' then
				new.paused = ''f'';
			else
				new.paused = ''t'';
			end if;
			new.clicks = 0;
		end if;
	else
		new.paused = ''t'';
		new.clicks = 0;
	end if;
	return new;
end;
'
LANGUAGE 'plpgsql' STRICT;

CREATE TRIGGER biu_bazarpayed BEFORE UPDATE OR INSERT ON bazar
	FOR EACH ROW EXECUTE PROCEDURE ch_payed_periodable();

CREATE TABLE advert_logs(
	advert int4 NOT NULL REFERENCES advert on delete cascade,
	service varchar(8) NOT NULL,
	date date NOT NULL default CURRENT_DATE,
	clicks int4 not null,
	views int4 not null,
	PRIMARY KEY (advert, service, date)
);

CREATE OR REPLACE FUNCTION log_advert_view(int4, text) RETURNS bool
AS '
declare
	p_advert_id alias for $1;
	p_service alias for $2;
	affected_rows int;
	p_service_type text;
begin
	p_service_type = check_parent(p_service);

	if p_service_type = ''periodables'' then
		EXECUTE ''UPDATE '' || p_service || '' SET tviews = tviews + 1 WHERE 
advert = '' || p_advert_id::text;
	else
		EXECUTE ''UPDATE '' || p_service || '' SET tviews = tviews - 1 WHERE 
advert = '' || p_advert_id::text;
	end if;
	
	UPDATE advert_logs SET views = views + 1 WHERE advert = p_advert_id AND 
service = p_service AND date = CURRENT_DATE;

	GET DIAGNOSTICS affected_rows = ROW_COUNT;

	if affected_rows = 0 then
		INSERT INTO advert_logs (advert, service, clicks, views) VALUES 
(p_advert_id, p_service, 0, 1);
	end if;

	return ''t'';
end;
'
LANGUAGE 'plpgsql' STRICT;


here is a php code, which defenetly under hi load makes something like 
deadlock (i do not exactly know, where - only it is in function 
log_advert_view())

first $query is something like for example c.bazar && '{3}' AND finish 
 >= CURRENT_DATE
or c.magazine && '{2}' AND c.tviews > 0
depending on what is going to be viewed (which service and which values 
are being interested in, service is something like magazine, dir, bazar)

$query = "SELECT a.* FROM advert AS a, $service AS c WHERE a.id = 
c.advert AND a.approved AND c.payed AND ".$query." AND NOT c.paused AND 
c.start <= CURRENT_DATE ORDER BY random() LIMIT $limit";
$data = false;
pg_query($connection, 'BEGIN WORK');
$result = array();
$res = @pg_query($connection, $query);
if ($num_rows = @pg_num_rows($res)) {
	for ($ii=0; $ii<$num_rows; ++$ii) {
		$data = pg_fetch_array($res, $ii, PGSQL_ASSOC);
		if (substr($_SERVER['REMOTE_ADDR'], 0, 11) <> '194.145.63.'){
			pg_query($connection, "SELECT log_advert_view('{$data['id']}'::int, 
'$service')");
		}
		ob_start();
		include '/var/www/reklama.dir.bg/adv/templates/'.$data['template'].'.php';
		$result[$ii] = ob_get_contents();
		ob_end_clean();
	}
	@pg_free_result($res);
	pg_query($connection, 'COMMIT WORK');
} else {
	@pg_free_result($res);
	pg_query($connection, 'ROLLBACK WORK');
}

so as I mentioned when a deadlock situation occurred there are a lot of 
SELECT log_advert_view() statment waiting in pg_stat_activity
I tried without transactions -> no difference
tried to remove intarray indexes -> no difference

the solution I found was this function log_advert_view() to be 
implemented in php -> many pg_query statements etc. AND without 
transactions AND without intarray indexes bazar_rdtree_idx, 
dir_rdtree_idx, magazine_rdtree_idx (all other combinations ended in 
deadlocks)

so my question is is there any explanation of why is it happening, and 
is there something I'm missing

maybe it is not deadlock, but what else it is ? (hundred processes got 
stuck)

if some other info would be valuable for the situation I will provide it

thanx
ceco




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Deadlock problem
@ 2003-04-23 18:28  Rod Taylor <rbt@rbt.ca>
  parent: Tzvetan Tzankov <ce@noxis.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Rod Taylor @ 2003-04-23 18:28 UTC (permalink / raw)
  To: Tzvetan Tzankov <ce@noxis.net>; +Cc: pgsql-sql


> $query = "SELECT a.* FROM advert AS a, $service AS c WHERE a.id = 
> c.advert AND a.approved AND c.payed AND ".$query." AND NOT c.paused AND 
> c.start <= CURRENT_DATE ORDER BY random() LIMIT $limit";


Looks like you take the results of this query and do work on each row. 
If LIMIT > 1, then you could get a deadlock.

SESSION 1: BEGIN;
SESSION 1: SELECT ...
SESSION 1: UPDATE ... WHERE 3
SESSION 2: BEGIN;
SESSION 2: SELECT ...
SESSION 2: UPDATE ... WHERE 4
SESSION 1: UPDATE ... WHERE 4
SESSION 2: UPDATE ... WHERE 3
<deadlock>

However, removing the PHP transaction would have eliminated that
possibility.  The same could happen if you have 2 services in mixed
order.  Fixing this can be done by putting the current select into a
subquery and ordering the results.


SELECT * FROM (<current query>) as tbl ORDER BY <columns>;

Another thing which may help is to lock the rows returned from the inner
select using FOR UPDATE.

> maybe it is not deadlock, but what else it is ? (hundred processes got 
> stuck)

Does PostgreSQL tell you that a deadlock occurred and kill off an
offending transaction (after about 10 seconds or so -- check the logs)? 
If not, it's probably not a deadlock in the database.  But you may find
that a PHP process is not quiting for some reason and is holding a locks
in an idle and open transaction.

Are you using Persistent connections in PHP for the database?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-04-23 18:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-18 09:06 Deadlock problem Tzvetan Tzankov <ce@noxis.net>
2003-04-23 18:28 ` Rod Taylor <rbt@rbt.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox