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 <a...@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 > a...@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 >
