Thread: Lock and read next

Lock and read next

From
"Girish Bajaj"
Date:

Is there a way if one transaction has locked a row in a table, the next transaction does not get blocked while reading the same row, but moves on to the next record in the table to read?

 

Example:

 

Transaction 1

Select * from table LIMIT 1 FOR UPDATE

--transaction takes 3 min to complete

 

 

Transaction 2

Select * from table LIMIT 1 FOR UPDATE

 

 

 

Here, transaction 2 blocks on the same record that transaction 1 has a write lock on (for 3 min). Is there a way to tell transaction 2 to move on and get the next writable record?

 

Thanks,

Girish

 

 

Re: Lock and read next

From
Ron Johnson
Date:
On Wed, 2003-09-03 at 16:56, Girish Bajaj wrote:
> Is there a way if one transaction has locked a row in a table, the
> next transaction does not get blocked while reading the same row, but
> moves on to the next record in the table to read?
>
> Example:
>
> Transaction 1
>
> Select * from table LIMIT 1 FOR UPDATE
>
> --transaction takes 3 min to complete

Why the heck do the transactions take 3 minutes??!!??!!

> Transaction 2
>
> Select * from table LIMIT 1 FOR UPDATE
>
> Here, transaction 2 blocks on the same record that transaction 1 has a
> write lock on (for 3 min). Is there a way to tell transaction 2 to
> move on and get the next writable record?

Does SQL even support that kind of semantics?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Man, I'm pretty. Hoo Hah!"
Johnny Bravo


Re: Lock and read next

From
"Girish Bajaj"
Date:
I don't know if sql supports this or not, but when I was using ms sql
server, it supports something called readpast lock hint.

My transactions are not really 3 min long. Its just a long running
transaction. And since multiple processes will be accessing the same table
(all do the same task), they should all be working on different tasks rather
than the same one. This is taken care of by the FOR UPDATE sql statement.
Process 2 should acquire a lock on another row somehow and not block.

Thanks,
Girish




-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Wednesday, September 03, 2003 5:37 PM
To: PgSQL Novice ML
Subject: Re: [NOVICE] Lock and read next

On Wed, 2003-09-03 at 16:56, Girish Bajaj wrote:
> Is there a way if one transaction has locked a row in a table, the
> next transaction does not get blocked while reading the same row, but
> moves on to the next record in the table to read?
>
> Example:
>
> Transaction 1
>
> Select * from table LIMIT 1 FOR UPDATE
>
> --transaction takes 3 min to complete

Why the heck do the transactions take 3 minutes??!!??!!

> Transaction 2
>
> Select * from table LIMIT 1 FOR UPDATE
>
> Here, transaction 2 blocks on the same record that transaction 1 has a
> write lock on (for 3 min). Is there a way to tell transaction 2 to
> move on and get the next writable record?

Does SQL even support that kind of semantics?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Man, I'm pretty. Hoo Hah!"
Johnny Bravo


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match