Lock ACCESS EXCLUSIVE and Select question !

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 1:10 PM, Andrew Sullivan <ajs@crankycanuck.ca>wrote:

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 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, 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 3:44 PM, Andrew Sullivan <ajs@crankycanuck.ca>wrote:

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 4:28 PM, Andrew Sullivan <ajs@crankycanuck.ca>wrote:

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

On Mon, Feb 28, 2011 at 5:21 PM, Andrew Sullivan <ajs@crankycanuck.ca>wrote:

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 Mon, Feb 28, 2011 at 8:13 PM, David Johnston <polobo@yahoo.com> wrote:

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

On Tue, Mar 1, 2011 at 9:33 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: