Re: Transactions

On Saturday 18 March 2006 00:19, Tom Lane wrote:

Kevin Brown <blargity@gmail.com> writes:

So when the dialog opens, it executes a BEGIN, and then based on whether
they clicked ok or cancel on the dialog, it'd COMMIT or ROLLBACK. This
worked fine for me in testing, but now that I have two people using this
app simulatneously, if they both click on ok at the same time, postgres
seemingly deadlocks.

Shouldn't happen; please provide a reproducible test case.

regards, tom lane

When the "deadlock" occurs:

kevin@ajbit:~> ps -elF | grep postgres
0 S postgres 17767     1  0  76   0 -  9908 -       4220   0 Mar08 ?        
00:00:01 /usr/bin/postmaster -D /var/lib/pgsql/data
1 S postgres 17769 17767  0  76   0 -  9973 -      12544   0 Mar08 ?        
00:00:09 postgres: writer process
1 S postgres 17770 17767  0  76   0 -  7475 -       2972   0 Mar08 ?        
00:00:00 postgres: stats buffer process
1 S postgres 17771 17770  0  75   0 -  7274 -       3044   0 Mar08 ?        
00:00:00 postgres: stats collector process
1 S postgres 29972 17767  0  76   0 - 10312 -      14016   0 Mar16 ?        
00:00:04 postgres: dli dli 127.0.0.1(53492) idle
1 S postgres  4072 17767  0  76   0 - 10261 -      12892   0 14:02 ?        
00:00:00 postgres: dli dli 127.0.0.1(42227) idle
1 S postgres  6020 17767  0  75   0 - 10585 -      15136   0 19:57 ?        
00:00:29 postgres: dli dli 127.0.0.1(39869) idle in transaction
1 S postgres  6195 17767  0  76   0 - 10323 semtim 14940   0 20:22 ?        
00:00:28 postgres: dli dli 127.0.0.1(37735) INSERT waiting
1 S postgres  6718 17767  0  76   0 - 10240 -       7504   0 21:50 ?        
00:00:00 postgres: dli dli 127.0.0.1(56292) idle
1 S postgres  6721 17767  0  77   0 - 10111 -       5340   0 21:51 ?        
00:00:00 postgres: dli dli 127.0.0.1(45934) idle
0 R kevin     6835  6816  0  78   0 -   663 -        468   0 22:00 pts/9    
00:00:00 grep postgres

You can see an insert is just waiting. On what? The server software never
unlocks and it's just sitting in an execute for postgres.

This only happens once a day, and only when 2 people are using it at once, and
using this dialog at once, so I can't provide test cases. Let me know what
else I can do to help troubleshoot.

Kevin Brown <blargity@gmail.com> writes:

When the "deadlock" occurs:

kevin@ajbit:~> ps -elF | grep postgres
1 S postgres 6020 17767 0 75 0 - 10585 - 15136 0 19:57 ?
00:00:29 postgres: dli dli 127.0.0.1(39869) idle in transaction
1 S postgres 6195 17767 0 76 0 - 10323 semtim 14940 0 20:22 ?
00:00:28 postgres: dli dli 127.0.0.1(37735) INSERT waiting

You can see an insert is just waiting. On what?

I'd bet it's waiting on the "idle in transaction" transaction, which is
waiting for a nonresponding client. But the pg_locks view would tell
you more.

regards, tom lane