Thread: Lock ACCESS EXCLUSIVE and Select question !

Hi everyone !

I'm using lock with ACCESS EXCLUSIVE in several of my tables to assure that only one process write in those tables at same time, this is blocking my SELECT, and this is what i want, but, deadlocks start to showing more and more in my logs when SELECTS failt to get his Share Lock mode.

My question is, can i use a lower mode for example "EXCLUSIVE", letting Select commands to read the table but avoiding that select reads the new rows inside my transaction ?

For example:

- lock table ticks with "some mode"
- start to inserting new rows in ticks
    - meanwhile another thread: select * from ticks (but this select not bring me the new rows)
- end of transaction

May be a little newbie question, but i cannot find this answer in http://www.postgresql.org/docs/8.1/static/explicit-locking.html or similar pages, i really want to know if new rows inserted in an open transaction will be read it by another threads or this new rows are invisible no matter the mode of the transaction.

Any help is very welcome ^_^

Cheers,
Alan Acosta

On Mon, Feb 28, 2011 at 12:43:58PM -0500, Alan Acosta wrote:

> I'm using lock with ACCESS EXCLUSIVE in several of my tables to assure that
> only one process write in those tables at same time

Why are you doing that?  It sounds like a bad idea to me.

But anyway, I believe that the SHARE lock (which is what CREATE INDEX
uses) ought to work.  It should prevent any concurrent data
alterations in the table.

Also,

> May be a little newbie question, but i cannot find this answer in
> http://www.postgresql.org/docs/8.1/static/explicit-locking.html or similar

those are the docs for 8.1.x.  Note that it was EOL'd last November:

http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

> pages, i really want to know if new rows inserted in an open transaction
> will be read it by another threads or this new rows are invisible no matter
> the mode of the transaction.

Rows inserted by an uncommitted transaction are invisible to everyone
else until the transaction commits.  Postgres doesn't have dirty
reads.  If you have an open transaction and you look at a table where
another transaction has committed, then you will or will not see the
resulting rows depending on whether you are in READ COMMITTED or
SERIALIZABLE isolation mode, respectively.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Andrew, thank you very much for reply !

I already update my bookmark for 8.3 which is my current version, http://www.postgresql.org/docs/8.3/static/explicit-locking.html, i see new things here, like a comparative table.

Yep, seems like ACCESS EXCLUSIVE is a bad idea, at least now :p ! i check your recommendation about to use SHARE mode, but in http://www.postgresql.org/docs/8.3/static/explicit-locking.html i see that SHARE mode doesn't lock against itself, so, another thread using the same mode will be able to access the tables for update ! or i'm reading bad Table 13-2. Conflicting lock modes. Meanwhile i understand well which mode to use in which case i reduce my lock level to EXCLUSIVE, which lock against itself but let SELECT to do his job !

Cheers,
Alan Acosta

On Mon, Feb 28, 2011 at 03:32:08PM -0500, Alan Acosta wrote:
> your recommendation about to use SHARE mode, but in
> http://www.postgresql.org/docs/8.3/static/explicit-locking.html i see that
> SHARE mode doesn't lock against itself, so, another thread using the same
> mode will be able to access the tables for update ! or i'm reading bad *Table
> 13-2. Conflicting lock modes*. Meanwhile i understand well which mode to use
> in which case i reduce my lock level to EXCLUSIVE, which lock against itself
> but let SELECT to do his job !

I think I might be misunderstanding you.  As I read the above, you're
using more than one thread on the same connection when a transaction
is open.  That is almost certainly a bad idea, if that's what you're doing.

There is no locking mode that blocks the same transaction from using
the table.  If so, your transaction wouldn't be able to do anything
with the locked table (including, presumably, release the lock).

I ask again why you think it's a good idea to prevent any other
transaction from writing into the table you're using.  I can think of
a couple cases where that would be necessary, but in almost every case
I've seen people do that it's from not understanding database
trasactions.  It's almost certainly the wrong thing.  If you said more
about what you're trying to do, maybe someone can help you.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

My threads use each one different conecctions, so the transactions are different, may be my bad English doesn't help to much, sorry for that !

My application is trying to generate a numbered place for a client inside a bus, and to avoid to sell the place number "5" to two people, so i need to avoid that two sellers to sell the same place to same time, when i start my project, i read about table lock and choose ACCESS EXCLUSIVE, cause blocks everything, in that time seems safe :p, but now i have more and more sellers and the application is throwing a lot deadlocks in simple SELECTs, i check my logs and notice that was because ACCESS EXCLUSIVE is taking a little more time now, and deadlocks arise !

In,

Table 13-2. Conflicting lock modes

Requested Lock Mode	Current Lock Mode
	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

I can see that ACCESS EXCLUSIVE and  EXCLUSIVE blocks each other on different transactions at different threads, but SHARE don't, or i'm reading bad the table ? I need only one process insert or update my tables in my transaction, no matter how many i have.

How can i know which mode is better to block in which case ?

Cheers,
Alan Acosta

On Mon, Feb 28, 2011 at 04:12:30PM -0500, Alan Acosta wrote:

> My application is trying to generate a numbered place for a client inside a
> bus, and to avoid to sell the place number "5" to two people, so i need to
> avoid that two sellers to sell the same place to same time, when i start my
> project, i read about table lock and choose ACCESS EXCLUSIVE, cause blocks
> everything, in that time seems safe :p, but now i have more and more sellers
> and the application is throwing a lot deadlocks in simple SELECTs, i check
> my logs and notice that was because ACCESS EXCLUSIVE is taking a little more
> time now, and deadlocks arise !

Ah.  Well, then, yeah, I think you're going to have some pain.  See more below.

> *Table 13-2. Conflicting lock modes*
> Requested Lock ModeCurrent Lock ModeACCESS SHAREROW SHAREROW EXCLUSIVESHARE
> UPDATE EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVEACCESS EXCLUSIVEACCESS
> SHARE       XROW SHARE      XXROW EXCLUSIVE    XXXXSHARE UPDATE EXCLUSIVE
> XXXXXSHARE  XX XXXSHARE ROW EXCLUSIVE  XXXXXXEXCLUSIVE XXXXXXXACCESS
> EXCLUSIVEXXXXXXXX
> I can see that ACCESS EXCLUSIVE and  EXCLUSIVE blocks each other on
> different transactions at different threads, but SHARE don't,

Share does not, but it does block other writes.  See the text in the manual:

    SHARE

    Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE
    ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This
    mode protects a table against concurrent data changes.

But I still don't think that's going to scale.

I think what you probably want is to SELECT FOR UPDATE the row you're
aiming to update later.  Alternatively, you could use some sort of
pessimistic locking strategy using either a field on the row or an
advisory lock.  For the latter, see the manual.  For the former, it's
something like this:

    - create a sequence seq.

    - add an integer column newcol (with a default of 0) to your
      table.

    - when you select, make sure you include newcol.  Suppose it's
      value is 0 in the row you want.

    - when you sell the seat, UPDATE the row SET newcol =
      nextval('seq') WHERE newcol = _previous_newcol_value [and some
      other criteria, like the seat number or whatever]

    - now, either you affect some number of rows >0, which means you
      made a sale, or else 0 rows are affected (because some other
      transaction sold this seat at the same time).  In the latter
      case, you have to try a new seat.

Hope that helps,

A


--
Andrew Sullivan
ajs@crankycanuck.ca

I really appreciate your help Andrew, and yep, i already starto to feel some pain lol. I suppose is true but is better to ask, SELECT FOR UPDATE is faster than LOCK ?

Thanks for the recommendations, i will check them ^_^

Cheers,
Alan Acosta

On Mon, Feb 28, 2011 at 05:13:11PM -0500, Alan Acosta wrote:
> I really appreciate your help Andrew, and yep, i already starto to feel some
> pain lol. I suppose is true but is better to ask, SELECT FOR UPDATE is
> faster than LOCK ?

SELECT FOR UPDATE locks the row you're trying to lock.  So it's
"faster" in that it's not completely serialized on one person's
actions.

As I understand your application, you have a list of seats.  You want
to sell every seat, and you want to make sure that each seat is sold
no more than once.  But you also want people to be able to see whether
a seat is sold yet, and it would be good if more than one person can
buy a (different) seat at the same time.

If you do SELECT FOR UPDATE on the seat that the person is selecting
(or, if you assign them, I guess ORDER BY random LIMIT 1), then you
lock that seat from being sold while the customer is deciding, but
other customers could buy a different seat.

What you're doing instead right now is locking the whole table,
thereby preventing any seat from being sold (or, at the moment, even
looked at) while one customer is deciding.

It is very rare that you want to be making explicit table locks in a
database application: you're foiling your transaction manager.  It is
sometimes necessary, but in this case it probably isn't.  What you're
really doing is pretending that the database is like a file on the
filesystem, and you need to flock it.  That's not how transactional
databases work, and that's the pain you're feeling.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

But i have a new problem there, i have no rows for update, i create new rows when the seats are sold, cause rarely the database knows the capacity of the bus, number of seats, even some bus doesn't have seat number 4 for example :p. So i cannot SELECT FOR UPDATE no existent rows, but i still need to lock in some way in order to check if seat number X was already sold or is free !

Alan Acosta

Alan Acosta wrote:
> But i have a new problem there, i have no rows for update, i create new rows
> when the seats are sold, cause rarely the database knows the capacity of the
> bus, number of seats, even some bus doesn't have seat number 4 for example
> :p. So i cannot SELECT FOR UPDATE no existent rows, but i still need to lock
> in some way in order to check if seat number X was already sold or is free !

Sounds like you need a uniqueness constraint of some sort to detect when the
seat has been sold already when you try the insert.

Bosco.

On Monday, February 28, 2011 2:39:07 pm Alan Acosta wrote:
> But i have a new problem there, i have no rows for update, i create new
> rows when the seats are sold, cause rarely the database knows the capacity
> of the bus, number of seats, even some bus doesn't have seat number 4 for
> example

So how does the customer know what seat to choose? Or to put it another way,
when the INSERT is done where does the seat number come from?

>
> :p. So i cannot SELECT FOR UPDATE no existent rows, but i still need to
> :lock
>
> in some way in order to check if seat number X was already sold or is free
> !
>
> Alan Acosta

--
Adrian Klaver
adrian.klaver@gmail.com

On Mon, Feb 28, 2011 at 05:39:07PM -0500, Alan Acosta wrote:
> But i have a new problem there, i have no rows for update, i create new rows
> when the seats are sold, cause rarely the database knows the capacity of the
> bus, number of seats, even some bus doesn't have seat number 4 for example
> :p. So i cannot SELECT FOR UPDATE no existent rows, but i still need to lock
> in some way in order to check if seat number X was already sold or is free !

Someone else suggests that you need a uniqueness constraint, and I
agree.  But I also don't understand how you can be ensuring not to
double-sell something if you don't have the list of inventory prior to
its being sold.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

As mentioned SELECT FOR UPDATE is likely your best option.  As for an
algorithm if you can find an airline or sporting event case study those two
domains have this problem solved already.  Barring that the following comes
to mind.

Create a record for every "seat" that needs to be sold.
You can list all unreserved seats at a given point in time then at the time
of attempted reservation you re-SELECT but this time with FOR UPDATE and
then immediately mark the seat as reserved (and when it was reserved).
Establish a policy that reservations last for "X minutes" and, using
application code, reset the reservation to OPEN if that time elapses.
If the application needs to restart you can scan the table for the
reservation time and reset any that have already expired while loading back
into memory all those that are still valid.

It really isn't that different than dispatching tasks to handlers (which is
what I do) and the FOR UPDATE works just fine.  I recommend using a pl/pgsql
function for implementation.  Return a reservationID if the seat has been
reserved for a specific user or return null if it could not be reserved.
You also have access to "RAISE" events.  Alternatively, you could output a
multi-column row with a Boolean true/false as one of the fields for
"reservation made" and have other message field for cases where it was not
made.

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Monday, February 28, 2011 4:28 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Lock ACCESS EXCLUSIVE and Select question !

On Mon, Feb 28, 2011 at 04:12:30PM -0500, Alan Acosta wrote:

> My application is trying to generate a numbered place for a client
> inside a bus, and to avoid to sell the place number "5" to two people,
> so i need to avoid that two sellers to sell the same place to same
> time, when i start my project, i read about table lock and choose
> ACCESS EXCLUSIVE, cause blocks everything, in that time seems safe :p,
> but now i have more and more sellers and the application is throwing a
> lot deadlocks in simple SELECTs, i check my logs and notice that was
> because ACCESS EXCLUSIVE is taking a little more time now, and deadlocks
arise !

Hi everyone, thanks for all your advice, i will take then in mind ^_^, yep it was a little difficult to know which seats i can sell, but it was one of the client request, some business constraints don't let me know how many seats have an specific bus even 5 minutes before departure, sometimes i know sometimes i don't, even sometimes when i know i have to change on fly this capacity, for example my bus crash just before departure, so i have to use a default averaged capacity. A human must have the final word about which bus departure, so the software must be very very open to changes.

Meanwhile, i reduce my lock level and even the CPU load is now lower LOL, is fantastic, thanks for your help, clients are now working better and faster than before ^_^, i still have a lot of to read about postgres.

Alan Acosta

On Tue, Mar 01, 2011 at 09:23:49AM -0500, Alan Acosta wrote:

> seats have an specific bus even 5 minutes before departure, sometimes i know
> sometimes i don't, even sometimes when i know i have to change on fly this
> capacity, for example my bus crash just before departure, so i have to use a
> default averaged capacity. A human must have the final word about which bus
> departure, so the software must be very very open to changes.

This still sounds a little odd to me, but there is another way to do
it, and someone suggested it in this thread.  If you're doing this
only with INSERT, then you just need to find some combination of
columns that needs to be unique (one of which is obviously the seat
number).  Then you'll get a unique violation when two people try to
insert the same data, and someone will lose.

Your application could catch this in a savepoint and try again with a
different seat number.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Yep i already have those columns and unique constraint, my issue isn't sell the seat two times, i was a lot of paranoiac about that and use a lock mode to restricted for that.
I will check if i can create rows for seats before sell and use update, so i can use SELECT FOR UPDATE and not use insert, having to lock the whole table to check if a seat is free or sold, but i have to solve several issues in the meantime, for example, to sell a seat for one month in future, will i have to create every single seat(row) in database to be able to know if is free or sold, and other stuff, even so, seems a new and good idea for me, is the first time i heard about SELECT FOR  UPDATE.

Another question !, can i combine SELECT FOR UPDATE and LOCK command on different tables at the same transaction ?

Alan Acosta