Thread: SELECT FOR UPDATE locks whole table
We have the following query:
SELECT certificate_id INTO TEMP TABLE x_certs FROM certificate WHERE cert_status = 0 AND certificate_id
BETWEEN1111 AND 2222 AND client_id IN (1, 2, 3) ORDER BY certificate_id FOR UPDATE;
Is there any reason that this query should lock the entire certificate table? Is there something strange because of the
INclause or because it is going
into a temporary table? This is a production server running 7.2.2 so perhaps
it is fixed in 7.3.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
It should lock only the rows you retrieved, but I have no idea how FOR
UPDATE and INTO TEMP behave. My guess is that it should work fine, but
I have never seen those two used together before.
---------------------------------------------------------------------------
D'Arcy J.M. Cain wrote:
> We have the following query:
>
> SELECT certificate_id
> INTO TEMP TABLE x_certs
> FROM certificate
> WHERE cert_status = 0 AND
> certificate_id BETWEEN 1111 AND 2222 AND
> client_id IN (1, 2, 3)
> ORDER BY certificate_id
> FOR UPDATE;
>
> Is there any reason that this query should lock the entire certificate table?
> Is there something strange because of the IN clause or because it is going
> into a temporary table? This is a production server running 7.2.2 so perhaps
> it is fixed in 7.3.
>
> --
> D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
> http://www.druid.net/darcy/ | and a sheep voting on
> +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> We have the following query:
> SELECT certificate_id
> INTO TEMP TABLE x_certs
> FROM certificate
> WHERE cert_status = 0 AND
> certificate_id BETWEEN 1111 AND 2222 AND
> client_id IN (1, 2, 3)
> ORDER BY certificate_id
> FOR UPDATE;
> Is there any reason that this query should lock the entire certificate
> table?
It should only lock the selected rows ... and does, in a quick test
here. Would you provide the test case that makes you think it's doing
otherwise?
regards, tom lane
On December 6, 2002 02:10 pm, Bruce Momjian wrote:
> It should lock only the rows you retrieved, but I have no idea how FOR
> UPDATE and INTO TEMP behave. My guess is that it should work fine, but
> I have never seen those two used together before.
Turns out that it wasn't the SELECT ... FOR UPDATE that was causing the
problem. I did a test like this.
> > SELECT certificate_id
> > INTO TEMP TABLE x_certs
> > FROM certificate
> > WHERE cert_status = 0 AND
> > certificate_id BETWEEN 1111 AND 2222 AND
> > client_id IN (1, 2, 3)
> > ORDER BY certificate_id
> > FOR UPDATE;
Basically this query after a BEGIN TRANSACTION except without the INTO part.
Then I went to another window and tried to update two certificates, one
inside and one outside the range. The first failed and the second succeeded
as expected. I then updated one of the certs in the range. After that I
could not update any certificates until I closed the transaction. Very weird.
I then built a new database and repeated the experiment with fresh, simple
tables and was able to confirm that normally PostgreSQL does NOT have this
behaviour so then I started thinking about differences between the simple
setup and our real production setup. One thing that I thought of was that
the real database has this trigger on certificate.
CREATE TRIGGER mk_cardnum BEFORE INSERT OR UPDATE ON certificate FOR EACH ROW EXECUTE PROCEDURE mk_cardnum
(cardnum,certificate_id, validation);
mk_cardnum is a C function that reads certificate_id and validation and
writes something into cardnum. My understanding is that this only affects
the row(s) being updated. It must since this table has over seven million
records and we would notice if it took minutes to do a simple update.
I couldn't find anything in the docs or web specifically about this. Does
anyone have any ideas?
Adding hackers as this may be an internal issue.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.