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.