Switching between terminals
Hi All, I 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 t1 and t2 in this database and I have the following fileds t1(a_id smallint, fn character(20), ln character(20), rt smallint)t2( c_id smallint, c_name character(20));The connection to the "postgres" database is established through two terminals;From the 1st terminal I give the following command1) begin transaction; update t2 set c_name = 'lock' where c_id = 1;From the 2nd terminal I give the following command2) begin transaction; update t1 set ln = 'lock' where a_id = 1;Then I come back to the 1st terminal and execute the following3) update t1 set ln = 'lock' where a_id = 1;Then I come to 2nd Terminal and execute the following 4) update t2 set c_name = 'lock' where c_id = 1;When I come out I get the following error message ERROR: 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 in 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 terminal I give the following command2) psql -f dl21.sql -U postgres -d postgresThen I come back to the 1st terminal and execute the following3) psql -f dl12.sql -U postgres -d postgresThen I come to 2nd Terminal and execute the following4) psql -f dl22.sql -U postgres -d postgresI should 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 is 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
Explore your hobbies and interests. Go to http://in.promos.yahoo.com/groups/
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.
On Thu, 2008-07-03 at 16:59 +0200, Csaba Nagy wrote:
If you'll say what you really want
to do, I bet you'll get a lot more useful advices...
Oh, and you should use the general list only for these kind of
questions, hackers is for discussion about hacking on the postgres code
itself. And cross-posting will also not help too much, the subscribers
on hackers which are likely to answer you are subscribed to the general
list too.
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 while I 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!
On Thu, 2008-07-03 at 16:59 +0200, Csaba Nagy wrote:
[snip] It is likely possible to do (using PREPARE TRANSACTION) [snip]
I was wrong, you can't do it with that either, see:
http://www.postgresql.org/docs/8.2/static/sql-prepare-transaction.html
Maybe there is some feature to attach/deattach to/from a session, but I
might be just confused...
Cheers,
Csaba.