[GENERAL] Locking question?
While debugging an application, I just wanted to confirm from the list here: Suppose I have a long running transaction which has a few updates and inserts running on some specific tables which means it has acquired Exclusive locks too during the transaction on specific table but if just before commit the client app crashes and the commit is never sent, will the Exclusive locks be automatically released? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com)
[GENERAL] locking question
I have a question about locks. I have a stats table that get updated when some other table changes. Sometimes that other table is updated a 2nd time before the first stats update is finished which causes an error. I've tried using 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' but get 'could not serialize access due to concurrent update' If i try 'READ COMMITED' i get primary key failures. This seems like it's a pretty common thing, and I'l like to be able to do this without having to write code to check for the 'could not serialize due to concurrent update' error and re-run the query. I don't have much experience with locking, because I haven't really needed to use it. Any advice would be greatly helpful. Belew is basically the transaction I'm running -- it fails when a 2nd one starts while the 1st is still running. BEGIN WORK delete from blah_stats where id = 1 insert into blah_stats select id,count(*) from blah where id = 1 group by id COMMIT WORK Regards, Brian Hirt ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Locking question?
Shoaib Mir wrote: > While debugging an application, I just wanted to confirm from the list here: > > Suppose I have a long running transaction which has a few updates and > inserts running on some specific tables which means it has acquired > Exclusive locks too during the transaction on specific table but if just > before commit the client app crashes and the commit is never sent, will the > Exclusive locks be automatically released? Yes (assuming the backend dies in the process, which may not happen if the app dies silently and while not waiting for anything from the server). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Locking question?
Thank you Alvaro :) - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/30/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Shoaib Mir wrote: > While debugging an application, I just wanted to confirm from the list here: > > Suppose I have a long running transaction which has a few updates and > inserts running on some specific tables which means it has acquired > Exclusive locks too during the transaction on specific table but if just > before commit the client app crashes and the commit is never sent, will the > Exclusive locks be automatically released? Yes (assuming the backend dies in the process, which may not happen if the app dies silently and while not waiting for anything from the server). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Re: [GENERAL] Locking question?
On 1/30/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Shoaib Mir wrote: > While debugging an application, I just wanted to confirm from the list here: > > Suppose I have a long running transaction which has a few updates and > inserts running on some specific tables which means it has acquired > Exclusive locks too during the transaction on specific table but if just > before commit the client app crashes and the commit is never sent, will the > Exclusive locks be automatically released? Yes (assuming the backend dies in the process, which may not happen if the app dies silently and while not waiting for anything from the server). Do you mean that the Ex-lock will be held indefinitely in the following situation" i) Appln. acquires Exclusive lock. ii) Appln. sleeps or is interacting with human. ii) Appln. crashes. Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] Locking question?
There is such timeout from the database server for the idle connections but yes you can always use firewall settings in order to do that and kill idle connections. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/6/07, Gurjeet Singh <[EMAIL PROTECTED]> wrote: On 1/30/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > Shoaib Mir wrote: > > While debugging an application, I just wanted to confirm from the list > here: > > > > Suppose I have a long running transaction which has a few updates and > > inserts running on some specific tables which means it has acquired > > Exclusive locks too during the transaction on specific table but if > just > > before commit the client app crashes and the commit is never sent, > will the > > Exclusive locks be automatically released? > > Yes (assuming the backend dies in the process, which may not happen if > the app dies silently and while not waiting for anything from the > server). > > Do you mean that the Ex-lock will be held indefinitely in the following situation" i) Appln. acquires Exclusive lock. ii) Appln. sleeps or is interacting with human. ii) Appln. crashes. Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] Locking question?
Well... if the application crashes then normally the TCP connection would drop as well. The problem is that in many environments it can take a *long* time for the backend to realize that the client went away. The tcp_keepalives_* settings are intended to try and reduce that time to a more reasonable value, but bear in mind that they depend on the TCP stack provided by the OS/environment, so it's not guaranteed to work perfectly. On Feb 6, 2007, at 3:21 AM, Shoaib Mir wrote: There is such timeout from the database server for the idle connections but yes you can always use firewall settings in order to do that and kill idle connections. -- Shoaib Mir EnterpriseDB ( www.enterprisedb.com) On 2/6/07, Gurjeet Singh <[EMAIL PROTECTED]> wrote: On 1/30/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Shoaib Mir wrote: > While debugging an application, I just wanted to confirm from the list here: > > Suppose I have a long running transaction which has a few updates and > inserts running on some specific tables which means it has acquired > Exclusive locks too during the transaction on specific table but if just > before commit the client app crashes and the commit is never sent, will the > Exclusive locks be automatically released? Yes (assuming the backend dies in the process, which may not happen if the app dies silently and while not waiting for anything from the server). Do you mean that the Ex-lock will be held indefinitely in the following situation" i) Appln. acquires Exclusive lock. ii) Appln. sleeps or is interacting with human. ii) Appln. crashes. Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] locking question - why is this not a deadlock?
i am using PostgreSQL 7.4.1 (i am aware of my need to upgrade :) i have a situation where i *believe* postgresql should be detecting a deadlock, but it is not. in the locks view you see below, all of the processes waiting on the 'numberfactory' table are blocked. (which smells like a deadlock to me). i killed pid 6829, and then i immediately saw the log entries that are at the bottom of this message regarding deadlocks amongst the remaining locks. pid 6829 was in that state of locks for close to an hour. * is this a bug? * if so, is this a known bug? fixed in the latest 7.4 release or would i have to migrate to 8.0? statement used to view locks: select ps.procpid, ps.query_start, ps.current_query, pc.relname, pl.mode, pl.granted from pg_locks pl left join pg_class pc on pl.relation = pc.oid, pg_stat_activity ps where pl.pid = ps.procpid order by ps.query_start; (I apologize for the wrapping, its also at http://pace2020.com/ ~proyal/locks.html) procpid | query_start | current_query| relname | mode | granted -+--- +- +--+--+- 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | numberfactory| AccessShareLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | numberfactory| RowExclusiveLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | csr | AccessShareLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | | ExclusiveLock| t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | | ShareLock| f 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | salesperson | AccessShareLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | jobbillingsetup | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | job | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | jobtype | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | jobbillingsetup | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | jobstatus | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | salesperson | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | | ExclusiveLock| t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | numberfactory | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | numberfactory | RowExclusiveLock | t 6856 | 2005-06-07 08:42:36.770765-04 | in transaction | csr | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | in transaction | salesperson | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | in transaction | shipvia | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | in transaction | pricelist | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | in transaction | | ExclusiveLock| t 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ShareLock| f 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory| AccessShareLock | t 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory| RowExclusiveLock | t 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ExclusiveLock| t 6857 | 2005-06-07 08:50:13.78227-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ExclusiveLock| t 6857 | 2005-06-07 08:50:13.78227-04 | UPDATE nu
Re: [GENERAL] locking question - why is this not a deadlock?
On Tue, Jun 07, 2005 at 10:26:00AM -0400, peter royal wrote: > i am using PostgreSQL 7.4.1 (i am aware of my need to upgrade :) > > i have a situation where i *believe* postgresql should be detecting a > deadlock, but it is not. in the locks view you see below, all of the > processes waiting on the 'numberfactory' table are blocked. (which > smells like a deadlock to me). i killed pid 6829, and then i > immediately saw the log entries that are at the bottom of this > message regarding deadlocks amongst the remaining locks. pid 6829 was > in that state of locks for close to an hour. > > * is this a bug? > * if so, is this a known bug? fixed in the latest 7.4 release or > would i have to migrate to 8.0? I don't think it's a bug, nor a deadlock situation. The problem is the "idle in transaction" server process, which holds some lock but isn't doing anything useful with it. Probably work would continue if the transaction was closed. This view is incomplete anyway, because you left out the xid column from the pg_locks view, which point out exactly to the locks that are blocking the other processes. I believe this problem wouldn't ocurr (or would be resolved in a different manner) in 8.1. I'm too lazy to replicate your scenario to check though ... > (I apologize for the wrapping, its also at http://pace2020.com/ > ~proyal/locks.html) The tags display funny there, but it's definitely easier to read. > deadlocks detected after i killed pid 6829: > > Jun 7 09:39:09 epace postgres[6857]: [4-1] ERROR: deadlock detected > Jun 7 09:39:09 epace postgres[6857]: [4-2] DETAIL: Process 6857 > waits for ShareLock on transaction 121712126; blocked by process 5234. Hmm, I guess this could be detected if the deadlock detection code was a lot more complicated than it already is, but probably it won't matter on 8.1. -- Alvaro Herrera () "¿Cómo puedes confiar en algo que pagas y que no ves, y no confiar en algo que te dan y te lo muestran?" (Germán Poo) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] locking question - why is this not a deadlock?
On Jun 7, 2005, at 1:15 PM, Alvaro Herrera wrote: I don't think it's a bug, nor a deadlock situation. The problem is the "idle in transaction" server process, which holds some lock but isn't doing anything useful with it. Probably work would continue if the transaction was closed. This view is incomplete anyway, because you left out the xid column from the pg_locks view, which point out exactly to the locks that are blocking the other processes. I believe this problem wouldn't ocurr (or would be resolved in a different manner) in 8.1. I'm too lazy to replicate your scenario to check though ... ah, thanks for the suggestion on what else to look for (first time debugging something like this in postgresql). i'll be better prepared to debug when it occurs again. the 'idle in transaction' is a (mis) feature of the JDBC driver that version of my software is using. (something that is fixed in the 8.0 JDBC driver thankfully, I'll likely start using that in the older version of my sw to help alleviate this) thanks again! -pete -- peter royal -> [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
