Lock and read next

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

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(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

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

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(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)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(dot)l(dot)johnson(at)cox(dot)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