[cheriyamoozhiyilcinu]

Hi All,
I am having an issue with a deadlock scenario in PostgreSQL 8.3.1
I 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 command
1) begin transaction; update t2 set c_name = 'lock' where c_id = 1;
From the 2nd terminal I give the following command
2) begin transaction; update t1 set ln = 'lock' where a_id = 1;
Then I come back to the 1st terminal and execute the following
3) 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 command
1) psql -f dl11.sql -U postgres -d postgres
From the 2nd terminal I give the following command
2) psql -f dl21.sql -U postgres -d postgres
Then I come back to the 1st terminal and execute the following
3) psql -f dl12.sql -U postgres -d postgres
Then I come to 2nd Terminal and execute the following
4) psql -f dl22.sql -U postgres -d postgres

I 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 advance
Waiting for reply
Regards
Cinu

[kakariko81280]

I'm not a PostgreSQL expert, but I would guess that the problem is the implicit completion of the transaction.

The transaction will belong to the psql session so when it quits back to the command prompt (implied by the ability to re-execute psql) it will either commit or rollback and close the transaction.

Each psql command will complete in this way so they will each get the database to themselves. No existing transactions leads to no deadlock.

May I ask why you are trying to engineer deadlocks? Not that it has any bearing on the answer, I'm just curious.

Let us know how you get on,

Chris...

[cheriyamoozhiyilcinu]

Actually I am trying to create a script that will do a lot of operations with the database, I am trying it with mostly all the flavours of the database.
I would like to create a deadlock scenario and try to get the solution of the same without entering the sql prompt.

[cheriyamoozhiyilcinu]

That infact worked, this was solved by introducing the pg_sleep function specific to postgreSQL.
This will create a delay in th transaction and then allow the deadlock to happen.