Re: Lock ACCESS EXCLUSIVE and Select question ! - Mailing list pgsql-general
| From | Alan Acosta |
|---|---|
| Subject | Re: Lock ACCESS EXCLUSIVE and Select question ! |
| Date | |
| Msg-id | AANLkTikqd98V-xom7xefGO4jJRO1QtCXaraWk2fkQGkH@mail.gmail.com Whole thread Raw |
| In response to | Re: Lock ACCESS EXCLUSIVE and Select question ! (Andrew Sullivan <ajs@crankycanuck.ca>) |
| Responses |
Re: Lock ACCESS EXCLUSIVE and Select question !
|
| List | pgsql-general |
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,
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
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 3:44 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
On Mon, Feb 28, 2011 at 03:32:08PM -0500, Alan Acosta wrote:I think I might be misunderstanding you. As I read the above, you're
> 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 !
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: