Thread: Switching between terminals
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top"><p>Hi All, <p><p>I am havingan issue with a deadlock scenario in PostgreSQL 8.3.1<p>I have the following database postgres, what I do is createtwo tables t1 and t2 in this database and I have the following fileds <p><p>t1(a_id smallint, fn character(20), lncharacter(20), rt smallint)<p>t2( c_id smallint, c_name character(20));<p><p>The connection to the "postgres" databaseis established through two terminals;<p><p><font color="#0000ff">From the 1st terminal I give the following command</font><p><fontcolor="#0000ff">1) begin transaction; update t2 set c_name = 'lock' where c_id = 1;</font><p><fontcolor="#0000ff"></font><p><font color="#0000ff">From the 2nd terminal I give the following command</font><p><fontcolor="#0000ff">2) begin transaction; update t1 set ln = 'lock' where a_id = 1;</font><p><font color="#0000ff"></font><p><fontcolor="#0000ff">Then I come back to the 1st terminal and execute the following</font><p><fontcolor="#0000ff">3) update t1 set ln = 'lock' where a_id = 1;</font><p><font color="#0000ff"></font><p><fontcolor="#0000ff">Then I come to 2nd Terminal and execute the following </font><p><font color="#0000ff">4)update t2 set c_name = 'lock' where c_id = 1;</font><p><p>When I come out I get the following error message <p><fontcolor="#ff0000">ERROR: deadlock detected<br />DETAIL: Process 15171 waits for ShareLock on transaction 12738;blocked by process 15183.<br />Process 15183 waits for ShareLock on transaction 12739; blocked by process 15171.</font><p><fontcolor="#ff0000"></font><p>This is perfectly fine, but what i am trying to acheive is that I am puttingthe above four queries in 4 different .sql files and executing it in the same way as displayed above by using twodifferent terminals, please refer below the sequence which I am using.<p><p><font color="#0000ff">From the 1st terminalI give the following command</font><p><font color="#0000ff">1) psql -f dl11.sql -U postgres -d postgres</font><p><fontcolor="#0000ff"></font><p><font color="#0000ff">From the 2nd terminal I give the following command</font><p><fontcolor="#0000ff">2) </font><font color="#0000ff">psql -f dl21.</font><font color="#0000ff">sql -U postgres-d postgres</font><p><font color="#0000ff"></font><p><font color="#0000ff">Then I come back to the 1st terminal andexecute the following</font><p><font color="#0000ff">3) </font><font color="#0000ff">psql -f dl12</font><font color="#0000ff">.sql-U postgres -d postgres</font><p><font color="#0000ff"></font><p><font color="#0000ff">Then I come to2nd Terminal and execute the following</font><p><font color="#0000ff">4) </font><font color="#0000ff">psql -f dl22</font><fontcolor="#0000ff">.sql -U postgres -d postgres</font><p><p>I should be getting the same message about deadlockdetection, but I am unable to get that.<p><p>Could anyone please tell me where I am going wrong and if there is away I can get the same behaviour that I am getting while I am executing the through psql prompt.<p><p>Thanks in advance<p><p>Waitingfor reply<p>Regards<p>Cinu<p><p><p><font color="#ff0000"><br /><br /></font></td></tr></table><br /><hrsize="1" /> From Chandigarh to Chennai - find friends all over India. <a href="http://in.rd.yahoo.com/tagline_groups_10/*http://in.promos.yahoo.com/groups/citygroups/">Click here.</a>
On Thu, 2008-07-03 at 19:56 +0530, cinu wrote: > Could anyone please tell me where I am going wrong and if there is a > way I can get the same behaviour that I am getting while I am > executing the through psql prompt. You're mistake is that you think a transaction is related to your terminal, but it is in fact tied to the psql session you are running... Your first example is running one psql instance per terminal, hence one transaction per terminal, while in your second example the transaction is terminated each time psql finishes to run. Basically what you're asking for is to keep a transaction opened by one session (the first psql execution) and connect to it with the second session (the second psql call) and continue the transaction which was opened by the first one... which I'm pretty sure is wrong to want. It is likely possible to do (using PREPARE TRANSACTION), but even likelier that it is a wrong thing to do in normal circumstances. If you'll say what you really want to do, I bet you'll get a lot more useful advices... Cheers, Csaba.
"cinu" <cheriyamoozhiyilcinu@yahoo.co.in> writes: Could anyone please tell me where I am going wrong and if there is a way I can get the same behaviour that I am getting whileI am executing the through psql prompt. a) you might try hitting return occasionally in your email :) b) you maybe need to put a SELECT pg_sleep(10) between the two queries in the first file you run so that it hasn't updated both tables and exited before the second one even starts. But I'm just guessing since you haven't sent the actual files you're running. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!