RE: [HACKERS] Lock on arbitrary string feature
In contrast the current alternatives appear to be either LOCK the entire table (preventing ALL inserts and selects), SHARE ROW EXCLUSIVE mode doesn't prevent selects... or to create a UNIQUE constraint (forcing complete rollbacks and restarts in event of a collision :( ). Hopefully, savepoints will be in 7.2 Any comments, suggestions or tips would be welcome. It looks like quite a complex thing to do - I've only just started looking at the postgresql internals and the lock manager. It's very easy to do (from my PoV -:)) We need in yet another pseudo table like one we use in XactLockTableInsert/XactLockTableWait - try to look there... Vadim
Re: [HACKERS] Lock on arbitrary string feature
On Thu, Jan 11, 2001 at 03:43:39PM +0800, Lincoln Yeoh wrote: Hi, Has anyone any input to offer on adding an arbitrary locking feature? Where GETLOCK "string" will lock on "string", the lock being only released at the end of a transaction. While the lock is held, other processes trying to do GETLOCK "string" will block until the lock is released. This feature can allow applications to better serialize things. For example: inserting unique records. Cooperating applications could just do something like: GETLOCK "mytable.key2=1234"; SELECT count(*) from mytable where key2=1234 for update; if count==0, insert the stuff. elsif count==1 update the stuff instead else something is wrong! The lock will thus only affect applications interested in mytable where key2=1234 We do something like this with listen/notify pairs. To syncronize two clients, we have them each listen for the other's token string, send a notify, and then block on select(), checking for incoming notifications. When they get the notification, they send a notify back to the other side to un-block it. If anything, it would be nice if there were a way to make a LISTEN block the connection on a specific event tag, which is essentially what we are doing in our interface library. -- Adam Haberlach|A cat spends her life conflicted between a [EMAIL PROTECTED] |deep, passionate, and profound desire for http://www.newsnipple.com |fish and an equally deep, passionate, and '88 EX500 |profound desire to avoid getting wet.
Re: [HACKERS] Lock on arbitrary string feature
Tom Lane wrote: Lincoln Yeoh [EMAIL PROTECTED] writes: Has anyone any input to offer on adding an arbitrary locking feature? Where GETLOCK "string" will lock on "string", the lock being only released at the end of a transaction. Any comments, suggestions or tips would be welcome. It looks like quite a complex thing to do - I've only just started looking at the postgresql internals and the lock manager. A lock is basically an entry in a shared hash table, so you could implement this just by having a different kind of key (ie, the given string) for these sorts of locks. However, the whole thing strikes me as more of an ugly kluge than a clean solution to the real problem. If you're not using a UNIQUE constraint then you're relying on application logic to guarantee consistency, which is bad. If you do have a UNIQUE constraint and want to layer this sort of application lock on top of it, then you still have the problem of unexpected failures if some instance/portion of your application does inserts without remembering to get the application-level lock. So, as Vadim remarked, doing the insert and rolling back to a savepoint on failure would be a much better answer. You're right that it's ugly, but at least it'd be a temporary "solution" for the fact that we cannot catch exceptions in triggers yet. So the if/else logic will currently not work reliable in a trigger without beeing able to lock before the SELECT. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
RE: [HACKERS] Lock on arbitrary string feature
At 09:20 AM 11-01-2001 -0800, Mikheev, Vadim wrote: In contrast the current alternatives appear to be either LOCK the entire table (preventing ALL inserts and selects), SHARE ROW EXCLUSIVE mode doesn't prevent selects... Sorry, I meant all inserts and selects on the locked table. At least so far it seems to block those selects in 7.0.3 (I hope it does in all cases! If not uhoh!). or to create a UNIQUE constraint (forcing complete rollbacks and restarts in event of a collision :( ). Hopefully, savepoints will be in 7.2 Yep that'll solve some things. Still think the getlock feature will be very handy in many other cases. BTW would there be a significant performance/resource hit with savepoints? Any comments, suggestions or tips would be welcome. It looks like quite a complex thing to do - I've only just started looking at the postgresql internals and the lock manager. It's very easy to do (from my PoV -:)) We need in yet another pseudo table like one we use in XactLockTableInsert/XactLockTableWait - try to look there... Thanks! I think by the time I succeed Postgresql will be version 7.2 or even 8 :). Cheerio, Link.
Re: [HACKERS] Lock on arbitrary string feature
At 01:26 PM 11-01-2001 -0500, Tom Lane wrote: Lincoln Yeoh [EMAIL PROTECTED] writes: GETLOCK "string" will lock on "string", the lock being only released at the end of a transaction. However, the whole thing strikes me as more of an ugly kluge than a clean solution to the real problem. If you're not using a UNIQUE But doesn't that go well with SQL :). The joys of INSERT vs UPDATE. And "select .. for update" too! So far I haven't left out any "for updates", at least I think so ;). I did consider using select for update to simulate it but it doesn't work when the values are very variable. application-level lock. So, as Vadim remarked, doing the insert and rolling back to a savepoint on failure would be a much better answer. Yep, savepoints will allow better consistency. But a getlock feature can be very handy in lots of other scenarios. BTW, you should consider whether you couldn't use the existing USERLOCK feature as a short-term alternative. If you can squeeze the key value you need to insert into a user lock tag, that will do as well as your proposed general-string-tag locks. Looks interesting. Probably what it does is similar enough to what I'm trying to do. Copy from the best :). But meantime, back to lock table... Cheerio, Link.
