Обсуждение: Switching between terminals

Поиск
Список
Период
Сортировка

Switching between terminals

От
cinu
Дата:
<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> 

Re: Switching between terminals

От
Csaba Nagy
Дата:
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.



Re: Switching between terminals

От
Gregory Stark
Дата:
"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!