Re: [HACKERS] Can pessimistic locking be emulated?
Just as a suggestion: In most of my applications, we have a security layer which is implemented through server-side functions. These functions keep a table updated which contains: lock_table record_id lock_user time_locked That's an excellent and even portable idea. This allows us to avoid nasty your update cannot be processed-type error messages by showing the user up front which records are locked, as well as allowing the admin to decide when locks should time out. I tend to find in general that database locking mechanisms are a very poor locking strategy for a good UI. True. But you circumvented it elegantly. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can pessimistic locking be emulated?
I am trying to emulate a pessimistic locking system you would find in an old school database file system, for example cobol. Generally, when a cobol program tries to read a record that is locked by somebody else, the read fails and either a message is displayed by the user or a error handling procedure is executed. I would like to emulate this behavior for legacy code while using mvcc for newer procedures I write. 4 questions: 1. Can you query if a tuple is locked by another transaction (the documentation unclearly suggests this can't be done via the pg_lock view) before executing select for update...? 2. If so, is this reasonable efficient to do, i.e. straight join on oid/xid? 3. If so, is this possible to query without a race condition regarding the lock status? 4. If so, is this likely to be possible in future versions of postgres without non-trivial changes? In other words, if User B attempts to select for update a record that user A has selected for update, it would be nice if User B's query would fail with a NOTICE to act upon. No idea if this is of any help, but you may have a look into PostgreSQL 7.3 Documentation 3.4. Run-time Configuration STATEMENT_TIMEOUT (integer) Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can pessimistic locking be emulated?
That's my fallback position. Obviously, this will lead to false positives depending on server load. In my case, I'm targeting between 30-50 users so its likely to throw timeouts for various reasons other than locks even though my queries of interest are generally select a from b where id = c type of thing. This is a kludgy solution but its still better than writing cobol. The bigger issue is that a timeout will not return the reason the query timed out. There are cases where I would like to run a select for update over a range of records and handle the locked records and unlocked records differently. A query that could match locked oids vs the oids I am interested in would be super. I could then aggregate my select for updates into larger queries and reap massive performance gains. Another way of putting it is this: waiting for your select to timeout is kind of like parking in Manhattan: you back your car up until you hit the next car. I would sort of like to, uh, look in the rear view mirror first. Merlin In other words, if User B attempts to select for update a record that user A has selected for update, it would be nice if User B's query would fail with a NOTICE to act upon. No idea if this is of any help, but you may have a look into PostgreSQL 7.3 Documentation 3.4. Run-time Configuration STATEMENT_TIMEOUT (integer) Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can pessimistic locking be emulated?
That's my fallback position. Obviously, this will lead to false positives depending on server load. In my case, I'm targeting between 30-50 users so its likely to throw timeouts for various reasons other than locks even though my queries of interest are generally select a from b where id =3D c type of thing. This is a kludgy solution but its still better than writing cobol. The bigger issue is that a timeout will not return the reason the query timed out. There are cases where I would like to run a select for update over a range of records and handle the locked records and unlocked records differently. A query that could match locked oids vs the oids I am interested in would be super. I could then aggregate my select for updates into larger queries and reap massive performance gains. Another way of putting it is this: waiting for your select to timeout is kind of like parking in Manhattan: you back your car up until you hit the next car. I would sort of like to, uh, look in the rear view mirror first. I see your point. 1. Can you query if a tuple is locked by another transaction (the documentation unclearly suggests this can't be done via the pg_lock view) before executing select for update...? Where did you find this? Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Can pessimistic locking be emulated?
I was referring to 10.3 in the administrator's guide, regarding the pg_lock view. According to the documentation, the view only contains table level locks. However, the view also contains an xid for transactions. The unclear part, at least to me, was what the role of the xid was in the view and if it could be used to produce a list of locked tuples somehow. The xid is referred to as a 'lockable object'. I wasn't sure of the xid's role in the mix. I see now how it all works together. In my case, being able to view outstanding row level locks would be enormously useful. I'm assuming this is not possible for structural or performance reasons. I'm aware of the possible nasty side affects of repeated query calls to the lock manager. I'm also aware what I'm asking about may be folly or silly, my understanding of how mvcc and transactions work together is not very refined. A curious thought struck me: does the pg_lock view follow the mvcc rules, i.e. if you query the pg_lock view inside a transaction, and an external effect introduces new locks into the server are you able to see those locks? Merlin 1. Can you query if a tuple is locked by another transaction (the documentation unclearly suggests this can't be done via the pg_lock view) before executing select for update...? Where did you find this? Regards, Christoph ---(end of broadcast)--- TIP 3: 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: [HACKERS] Can pessimistic locking be emulated?
Merlin Moncure [EMAIL PROTECTED] writes: In my case, being able to view outstanding row level locks would be enormously useful. The only way to do that would be to grovel through every table in the database, looking for rows that are marked locked by transactions that are still alive. A curious thought struck me: does the pg_lock view follow the mvcc rules, No, not really. If it did I don't think it'd be real useful ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can pessimistic locking be emulated?
In my case, being able to view outstanding row level locks would be enormously useful. I'm assuming this is not possible for structural or Agreed -- but they're stored on the row themselves. You might be able to write a function which executes dirty reads on the table and tells you if the row is locked or not, but it's not going to be simple. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Can pessimistic locking be emulated?
Rod Taylor [EMAIL PROTECTED] writes: Agreed -- but they're stored on the row themselves. You might be able to write a function which executes dirty reads on the table and tells you if the row is locked or not, but it's not going to be simple. Actually, I don't think you need a dirty read at all. A locked row can't be deleted as well (because there's only one xmax slot), so if you can see it (ie, you think its xmin is committed) then you can in principle find out whether it's locked or not. We just don't expose the info at the moment. (You can see xmax at the user level, but you can't easily tell if xmax is trying to delete the row or just lock it, because you don't have access to the infomask bit that would tell you.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can pessimistic locking be emulated?
On Thu, 2003-02-27 at 15:02, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Agreed -- but they're stored on the row themselves. You might be able to write a function which executes dirty reads on the table and tells you if the row is locked or not, but it's not going to be simple. Actually, I don't think you need a dirty read at all. A locked row I see. That will make it quite a bit easier then. Perhaps I'll write a function sometime. It would make it useful for fetching things out of a persistent work queue. Right now I deal with userlocks -- but those can be clumsy. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Can pessimistic locking be emulated?
This directly answers my question (wasn't previously aware that xid could be queried out in such a useful fashion). Not only does this accomplish what I need, but now allows me to not use select ... for update and stick with a transaction based locking mechanism. The 'Why' isn't that interesting in my case: merely that the knowledge that the record is involved in a transaction is enough. I've felt for a while that the descriptions of transactions, mvcc, and row level locking in the official docs could use a little bit better treatment (selfishly motivated, I could never figure them completely out!) but this is the wrong list for that :). Many thanks to the hackers for helping me with my problem. Merlin Actually, I don't think you need a dirty read at all. A locked row can't be deleted as well (because there's only one xmax slot), so if you can see it (ie, you think its xmin is committed) then you can in principle find out whether it's locked or not. We just don't expose the info at the moment. (You can see xmax at the user level, but you can't easily tell if xmax is trying to delete the row or just lock it, because you don't have access to the infomask bit that would tell you.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can pessimistic locking be emulated?
Merlin, Just as a suggestion: In most of my applications, we have a security layer which is implemented through server-side functions. These functions keep a table updated which contains: lock_table record_id lock_user time_locked This allows us to avoid nasty your update cannot be processed-type error messages by showing the user up front which records are locked, as well as allowing the admin to decide when locks should time out. I tend to find in general that database locking mechanisms are a very poor locking strategy for a good UI. -- Josh Berkus [EMAIL PROTECTED] Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
