Re: [GENERAL] table locks
[EMAIL PROTECTED] wrote: >The question is *why* you feel you need that, ie what are you using >these for? As was already mentioned upthread, it's usually better >to avoid explicit locking altogether, if you can. Scenario 1. One has objects belong to a metaclass. Certain operations changes metaclass while some other operations are sensitive to these changes and could not be executed during changes. Metaclass corresponds with a table and it is very convenient to be able to lock such a table exclusively or shared. Scenario 2. One should calculate number of items (or amount of money on several accounts) and do some actions based on these calculations. Calculations and analysis done on user side. Data analysis and data modification should be done in the same transaction because of obvious reason. The problem it is not enough, and serializable isolation level [may be] required here. Other problem one cannon use PostgreSQL▓s serializable transaction isolation because of number of conflicts. My own case is similar with Scenario 2 while it is rather special. In my company PostgreSQL is used as a backend (among other DBMSs) by an application. This application expects DBMS provide real and proper serializable isolation level. Sometimes this problem could be solved (or bypassed), though my abilities to change application logic are limited and sometimes the only option is table locks. I have very long experience with Oracle. During those years I▓ve never used table locks and never thought about such a thing at all, so I understand my questions look peculiar. Actually they are not as strange as they probably seem. >> The only drawback - interference with VACUUM and other system processes >> with obvious performance/response time penalty. > >I can hardly imagine an ordinary lock type that doesn't conflict with >anything at all ... ALTER/DROP TABLE being the obvious counterexamples. That▓s true and DELETE/UPDATE/INSERT statements are other counterexamples. The idea is I don▓t care about anything bypass application logic. It is not perfect, but it is acceptable. It's nature of advisory locks after all. >If you don't want your "shared" lock to conflict with VACUUM then you >could use ACCESS SHARE instead of SHARE, and it would still block >EXCLUSIVE. So you suggest I use ACCESS SHARE instead of SHARE. It means I should use ACCESS EXCLUSIVE instead of EXCLUSIVE, right? Not sure it is better pair then SHARED/EXCLUSIVE because ACCESS EXCLUSIVE blocks even SELECTs, while I give it a try. >It's quite unlikely to get accepted, considering that advisory locks >already seem to cover the territory. (8.2 has blocking versions of >those calls BTW.) Thank you for information about advisory locks. It is cool we have blocking versions now. Next step is advisory locks with transaction behavior, right? It would be very very nice. Not sure my English is good enough to put adjectives in proper order ;) , but what I need is advisory [table] blocking transaction lock. Table-level is Ok for me while general form is probably better. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] table locks
"Ilja Golshtein" <[EMAIL PROTECTED]> writes: > I need table level locks for cooperative usage in my application. > LOCK TABLE table_name in EXCLUSIVE MODE > and > LOCK TABLE table_name in SHARED MODE > perfectly suit my needs. The question is *why* you feel you need that, ie what are you using these for? As was already mentioned upthread, it's usually better to avoid explicit locking altogether, if you can. > The only drawback - interference with VACUUM and other system processes > with obvious performance/response time penalty. I can hardly imagine an ordinary lock type that doesn't conflict with anything at all ... ALTER/DROP TABLE being the obvious counterexamples. If you don't want your "shared" lock to conflict with VACUUM then you could use ACCESS SHARE instead of SHARE, and it would still block EXCLUSIVE. > Honestly I've already introduced such locks with syntax > LOCK TABLE table_name in APPLICATION EXCLUSIVE MODE > and > LOCK TABLE table_name in APPLICATION SHARED MODE > Does publishing of this patch make any sense? It's quite unlikely to get accepted, considering that advisory locks already seem to cover the territory. (8.2 has blocking versions of those calls BTW.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] table locks
>>> Why do you want to lock at all? > >> It's long and sad story ;( > >If you don't explain what you're trying to accomplish, you're unlikely >to get useful advice. Tom, I need table level locks for cooperative usage in my application. LOCK TABLE table_name in EXCLUSIVE MODE and LOCK TABLE table_name in SHARED MODE perfectly suit my needs. The only drawback - interference with VACUUM and other system processes with obvious performance/response time penalty. So I need very simple thing: locks behave exactly like EXCLUSIVE and SHARED I can use to control my own application processes. Honestly I've already introduced such locks with syntax LOCK TABLE table_name in APPLICATION EXCLUSIVE MODE and LOCK TABLE table_name in APPLICATION SHARED MODE Does publishing of this patch make any sense? I can explain what is the reason of table locks usage (briefly I need this thing to provide transaction isolation). Story is long and sad indeed, though I can retell it if it seems to be useful. Thanks a lot for your response. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] table locks
"Ilja Golshtein" <[EMAIL PROTECTED]> writes: >> Why do you want to lock at all? > It's long and sad story ;( If you don't explain what you're trying to accomplish, you're unlikely to get useful advice. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] table locks
>But seriously, why block autovacuum? It no visible effect on the table. I DO NOT want to block autovacuum. I need lock modes do not interfere with anything else just to use in my application. user_locks is fine, while it does not provide waiting (only immediate Yes or No). >Why do you want to lock at all? It's long and sad story ;( -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] table locks
On Mon, Dec 25, 2006 at 02:58:26PM +0300, Ilja Golshtein wrote: > Hello! > > I need table locks to provide application logic. Just very common lock types > with very common behavior - Shared and Exclusive. > > Which PostgreSQL's lock modes should be preferred in order to avoid conflicts > with autovacuum and suchlike? The documentation on locks clearly lists what lock types conflict with what. But seriously, why block autovacuum? It no visible effect on the table. Why do you want to lock at all? Lock-free designs are always better. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Table locks and serializable transactions.
On 3/11/06, Bill Moseley <[EMAIL PROTECTED]> wrote: > I need to insert a row, but how that row is inserted depends on the > number of items existing in the table. I initially thought > SERIALIZABLE would help, but that only keeps me from seeing changes > until the commit in that session. serializable transactions ensure that any data read in-transaction (but not locked) stays consistent for the remainder of the transaction. It prevents the following in pseudo sql: begin; select into account_balance balance from account where account_id = xxx; [do some stuff that generates n from account_balance] update account set balance = balance + n where account_id = xxx; commit; if you are not using serializable transactions, there is a race on balance getting updated because it isn't locked on the select. Now, you could lock it on the select by adding 'for update' which would more or less eliminate the need to serialize *if all the transactions that modify balance follow this access pattern*. now, on to your problem. > Am I correct that if I need to insert a row into a table that contains > column info based on the state of the table I need to lock the table > in "share row exclusive mode"? yes. IIRC this allows non locking readers to read the table but serializes locking writers which is exactly what you want. Note that this means that two reservations cannot occur at the same time. But since you defined the problem that one reservation may affect how the next one is granted, this is pretty much the only way unless you get into a lazy evaluation of reservation state. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Table locks
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > No spinlock. I was going to just grab a snapshot as it existed. If it > > changes while I am grabbing it, I just try again. > > Unless, of course, you follow a now-dangling pointer and cause a backend > crash. But even without that, how do you know whether you grabbed a > self-consistent snapshot or not? Not sure, yet... -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Table locks
Bruce Momjian <[EMAIL PROTECTED]> writes: > No spinlock. I was going to just grab a snapshot as it existed. If it > changes while I am grabbing it, I just try again. Unless, of course, you follow a now-dangling pointer and cause a backend crash. But even without that, how do you know whether you grabbed a self-consistent snapshot or not? regards, tom lane
Re: [GENERAL] Table locks
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > I am thinking of a tcl/tk app that can go in and grab information from > > backends by querying the actual structure values. Does gdb > > automatically halt the running app? > > Yes. I suppose this is no big problem if you start a backend to be > used only as the gdb target, but you'd not want to take over a live > client's backend for the purpose. That was the idea. > A bigger problem is that it's not a portable approach, since gdb may not > be available/installed on a given platform. Furthermore, you won't get > far unless the installed executable was compiled with debug symbols, > which isn't (and IMHO shouldn't be) the default configuration. Good point. I certainly did not want to muck up the backend with an API just so I could grab information for a monitoring utility. > > Finally (and probably the key point): what are you going to do about > locking? You aren't going to be able to grab a spinlock via gdb, nor > would it be a good idea if you could --- holding down a critical > spinlock while a tcl-to-gdb-to-backend conversation goes on would be a > killer for performance. No spinlock. I was going to just grab a snapshot as it existed. If it changes while I am grabbing it, I just try again. > > I think the information-grabbing routines need to be C code in the > backend. Not sure. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Table locks
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am thinking of a tcl/tk app that can go in and grab information from > backends by querying the actual structure values. Does gdb > automatically halt the running app? Yes. I suppose this is no big problem if you start a backend to be used only as the gdb target, but you'd not want to take over a live client's backend for the purpose. A bigger problem is that it's not a portable approach, since gdb may not be available/installed on a given platform. Furthermore, you won't get far unless the installed executable was compiled with debug symbols, which isn't (and IMHO shouldn't be) the default configuration. Finally (and probably the key point): what are you going to do about locking? You aren't going to be able to grab a spinlock via gdb, nor would it be a good idea if you could --- holding down a critical spinlock while a tcl-to-gdb-to-backend conversation goes on would be a killer for performance. I think the information-grabbing routines need to be C code in the backend. regards, tom lane
Re: [GENERAL] Table locks
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > I want to write an admin utility that will view backend SQL queries, and > > be able to view locks and statistics using gdb on the running backend. > > gdb on a backend doesn't seem to me like a reasonable component of a > production situation. I was thinking something along the line of a > SHOW command that would show the state of the lock table. I am thinking of a tcl/tk app that can go in and grab information from backends by querying the actual structure values. Does gdb automatically halt the running app? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Table locks
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > I want to write an admin utility that will view backend SQL queries, and > > be able to view locks and statistics using gdb on the running backend. > > gdb on a backend doesn't seem to me like a reasonable component of a > production situation. I was thinking something along the line of a > SHOW command that would show the state of the lock table. Yes, SHOW would be nicer. I was thinking of a more general solution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Table locks
Bruce Momjian <[EMAIL PROTECTED]> writes: > I want to write an admin utility that will view backend SQL queries, and > be able to view locks and statistics using gdb on the running backend. gdb on a backend doesn't seem to me like a reasonable component of a production situation. I was thinking something along the line of a SHOW command that would show the state of the lock table. regards, tom lane
Re: [GENERAL] Table locks
I want to write an admin utility that will view backend SQL queries, and be able to view locks and statistics using gdb on the running backend. > "Dale Anderson" <[EMAIL PROTECTED]> writes: > > Is there any way to view the locks being held on a table?? > > There is not any good way --- lock.c has a routine called DumpAllLocks, > but it's not even compiled by default, let alone connected up to any > reasonable calling method. I've been thinking for awhile that we > desperately need some simple way of dumping out the state of the lock > manager... > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
