--0-367516029-1215095216=:66139
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Hi All,=C2=A0I am having an issue with a deadlock scenario in PostgreSQL
8.=
3.1I have the following database postgres, what I do is create two tables
t=
1 and t2 in this database and I have the following fileds=C2=A0t1(a_id
smal=
lint, fn character(20), ln character(20), rt smallint)t2( c_id smallint,
c_=
name character(20));The connection to the "postgres" database is
establishe=
d through two terminals;From the 1st terminal I give the following
command1=
) begin transaction; update t2 set c_name =3D 'lock' where c_id =3D 1;From
=
the 2nd terminal I give the following command2) begin transaction; update
t=
1 set ln =3D 'lock' where a_id =3D 1;Then I come back to the 1st terminal
a=
nd execute the following3) update t1 set ln =3D 'lock' where a_id =3D
1;The=
n I come to 2nd Terminal and execute the following=C2=A04) update t2 set
c_=
name =3D 'lock' where c_id =3D 1;When I come out I get the following error
=
message=C2=A0ERROR: deadlock detected
DETAIL: Process 15171 waits for ShareLock on transaction 12738; blocked
by=
process 15183.
Process 15183 waits for ShareLock on transaction 12739; blocked by process
=
15171.This is perfectly fine, but what i am trying to acheive is that I am
=
putting the above four queries in 4 different .sql files and executing it
i=
n the same way as displayed above by using two different terminals, please
=
refer below the sequence which I am using.From the 1st terminal I give the
=
following command1) psql -f dl11.sql -U postgres -d postgresFrom the 2nd
te=
rminal I give the following command2)=C2=A0psql -f dl21.sql -U postgres -d
=
postgresThen I come back to the 1st terminal and execute the
following3)=C2=
=A0psql -f dl12.sql -U postgres -d postgresThen I come to 2nd Terminal and
=
execute the following4)=C2=A0psql -f dl22.sql -U postgres -d postgresI
shou=
ld be getting the same message about deadlock detection, but I am unable
to=
get that.Could anyone please tell me where I am going wrong and if there
i=
s a way I can get the same behaviour that I am getting while I am
executing=
the
through psql prompt.Thanks in advanceWaiting for replyRegardsCinu
=0A=0A=0A Explore your hobbies and interests. Go to
http://in.promos.y=
ahoo.com/groups/
--0-367516029-1215095216=:66139
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
<table cellspacing=3D'0' cellpadding=3D'0' border=3D'0' ><tr><td
valign=3D'=
top' style=3D'font: inherit;'><P>Hi All,=C2=A0</P><P></P><P>I am having an
=
issue with a deadlock scenario in PostgreSQL 8.3.1</P><P>I have the
followi=
ng database postgres, what I do is create two tables t1 and t2 in this
data=
base and I have the following fileds=C2=A0</P><P></P><P>t1(a_id smallint,
f=
n character(20), ln character(20), rt smallint)</P><P>t2( c_id smallint,
c_=
name character(20));</P><P></P><P>The connection to the "postgres"
database=
is established through two terminals;</P><P></P><P><FONT
color=3D"#0000ff"=
>From the 1st terminal I give the following command</FONT></P><P><FONT
colo=
r=3D"#0000ff">1) begin transaction; update t2 set c_name =3D 'lock' where
c=
_id =3D 1;</FONT></P><P><FONT color=3D"#0000ff"></FONT></P><P><FONT color=
=3D"#0000ff">From the 2nd terminal I give the following
command</FONT></P><=
P><FONT color=3D"#0000ff">2) begin transaction; update t1 set ln =3D
'lock'=
where a_id =3D
1;</FONT></P><P><FONT color=3D"#0000ff"></FONT></P><P><FONT
color=3D"#0000=
ff">Then I come back to the 1st terminal and execute the
following</FONT></=
P><P><FONT color=3D"#0000ff">3) update t1 set ln =3D 'lock' where a_id =3D
=
1;</FONT></P><P><FONT color=3D"#0000ff"></FONT></P><P><FONT
color=3D"#0000f=
f">Then I come to 2nd Terminal and execute the
following=C2=A0</FONT></P><P=
><FONT color=3D"#0000ff">4) update t2 set c_name =3D 'lock' where c_id =3D
=
1;</FONT></P><P></P><P>When I come out I get the following error
message=C2=
=A0</P><P><FONT color=3D"#ff0000">ERROR: deadlock detected<BR>DETAIL:
Pro=
cess 15171 waits for ShareLock on transaction 12738; blocked by process
151=
83.<BR>Process 15183 waits for ShareLock on transaction 12739; blocked by
p=
rocess 15171.</FONT></P><P><FONT color=3D"#ff0000"></FONT></P><P>This is
pe=
rfectly fine, but what i am trying to acheive is that I am putting the
abov=
e four queries in 4 different .sql files and executing it in the same way
a=
s displayed above by
using two different terminals, please refer below the sequence which I am
=
using.</P><P></P><P><FONT color=3D"#0000ff">From the 1st terminal I give
th=
e following command</FONT></P><P><FONT color=3D"#0000ff">1) psql -f
dl11.sq=
l -U postgres -d postgres</FONT></P><P><FONT
color=3D"#0000ff"></FONT></P><=
P><FONT color=3D"#0000ff">From the 2nd terminal I give the following
comman=
d</FONT></P><P><FONT color=3D"#0000ff">2)=C2=A0</FONT><FONT
color=3D"#0000f=
f">psql -f dl21.</FONT><FONT color=3D"#0000ff">sql -U postgres -d
postgres<=
/FONT></P><P><FONT color=3D"#0000ff"></FONT></P><P><FONT
color=3D"#0000ff">=
Then I come back to the 1st terminal and execute the
following</FONT></P><P=
><FONT color=3D"#0000ff">3)=C2=A0</FONT><FONT color=3D"#0000ff">psql -f
dl1=
2</FONT><FONT color=3D"#0000ff">.sql -U postgres -d
postgres</FONT></P><P><=
FONT color=3D"#0000ff"></FONT></P><P><FONT color=3D"#0000ff">Then I come
to=
2nd Terminal and execute the following</FONT></P><P><FONT
color=3D"#0000ff=
">4)=C2=A0</FONT><FONT
color=3D"#0000ff">psql -f dl22</FONT><FONT color=3D"#0000ff">.sql -U
postg=
res -d postgres</FONT></P><P></P><P>I should be getting the same message
ab=
out deadlock detection, but I am unable to get that.</P><P></P><P>Could
any=
one please tell me where I am going wrong and if there is a way I can get
t=
he same behaviour that I am getting while I am executing the through psql
p=
rompt.</P><P></P><P>Thanks in advance</P><P></P><P>Waiting for
reply</P><P>=
Regards</P><P>Cinu</P><P></P><P></P><P><FONT
color=3D"#ff0000"><BR><BR></FO=
NT></P></td></tr></table><br>=0A=0A=0A <!--10--><hr size=3D1></hr>
Fro=
m Chandigarh to Chennai - find friends all over India. <a
href=3D"http://in=
..rd.yahoo.com/tagline_groups_10/*http://in.promos.yahoo.com/groups/citygrou=
ps/"> Click here.</a>
--0-367516029-1215095216=:66139--
|
