pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDeadlocks? What happened to MVCC?
4+ messages / 3 participants
[nested] [flat]
* Deadlocks? What happened to MVCC?
@ 2001-08-14 19:15 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 4+ messages in thread
From: Josh Berkus @ 2001-08-14 19:15 UTC (permalink / raw)
To: pgsql-sql
Folks:
I'm getting deadlock errors on one of the operations on my web
application. It's a function which adds a large number of rows to a
holding table, then updates that set of rows multiple times in order to
present scoring information to the user.
However, the function is deadlocking itself once it's in heavy use. I
though that MVCC was supposed to supercede deadlocks, unless I
specifically set locks, which I'm not.
Help, anyone?
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Deadlocks? What happened to MVCC?
@ 2001-08-14 20:46 Jan Wieck <JanWieck@Yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jan Wieck @ 2001-08-14 20:46 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Josh Berkus wrote:
> Folks:
>
> I'm getting deadlock errors on one of the operations on my web
> application. It's a function which adds a large number of rows to a
> holding table, then updates that set of rows multiple times in order to
> present scoring information to the user.
>
> However, the function is deadlocking itself once it's in heavy use. I
> though that MVCC was supposed to supercede deadlocks, unless I
> specifically set locks, which I'm not.
>
> Help, anyone?
You must have misunderstood something. MVCC does not
supersede deadlocks, nor can it prevent them. All MVCC does
is to help "readers" not beeing blocked by "writers" and vice
versa, by avoiding the need for shared "read" locks. But
writers still need locks, so if your application does
something like
Xact-1: Starts transaction
Xact-2: Starts transaction
Xact-1: Updates row A
Xact-2: Updates row B
Xact-1: Wants to update row B (blocks)
Xact-2: Wants to update row A
then it's a deadlock. A deadlock is a deadlock and remains to
be a deadlock, no matter if you have versioning or not.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Deadlocks? What happened to MVCC?
@ 2001-08-14 21:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2001-08-14 21:35 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
MVCC does not mean "no locks" ... particularly not when UPDATEs are
involved. You'll need to be more specific about what your function is
doing, but my first thought would be to look for the possibility of
conflicting updates of the same row.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Deadlocks? What happened to MVCC?
@ 2001-08-14 22:44 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-08-14 22:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom, Jan,
> MVCC does not mean "no locks" ... particularly not when UPDATEs are
> involved. You'll need to be more specific about what your function
> is
> doing, but my first thought would be to look for the possibility of
> conflicting updates of the same row.
Found the problem after 3 hours and a phone call to PostgreSQL Inc.
Turns out that I had one too-broad UPDATE in a 250-line function ...
feh!
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-08-14 22:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-14 19:15 Deadlocks? What happened to MVCC? Josh Berkus <josh@agliodbs.com>
2001-08-14 20:46 ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-14 21:35 ` Re: Deadlocks? What happened to MVCC? Tom Lane <tgl@sss.pgh.pa.us>
2001-08-14 22:44 ` Re: Deadlocks? What happened to MVCC? Josh Berkus <josh@agliodbs.com>
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