Re: deadlock detected when inserting data - Mailing list pgsql-general

Thanks for your quick response.

I did take some database statistics during the run.  I posted at:
http://developer.osdl.org/jenny/deadlock/db_stat/

In the db_activity?.out, all the database connections were working on
shopping_cart procedure.  Also, I noticed that tran_lock?.out shows that
the same transaction(shopping_cart) requires multiple locks and not all
of them are granted.  Since shopping_cart is a storedprocedure (or
function) written in plpgsql,  and it calls other functions, is it
possible that the transaction is too long and holds the locks?

Another question is, I've read that "Each PL/pgSQL function is
automatically treated as a single transaction
by Postgres" at http://www.geocrawler.com/archives/3/6/2002/6/0/9050299/
is it still true?  Even though the shopping_cart function calls A and A
has BEGIN and COMMIT in it, pgsql won't commit till all the shopping_cart is done?

Thanks,
Jenny
On Mon, 2003-12-15 at 12:51, Ian Harding wrote:
> Deadlocks are hard to figure out sometimes.  They don't necessarily have
> to do with foreign key constraints.  They happen when two transactions
> are waiting for each other to finish what they are doing before they
> proceed.  Instead of waiting forever, a deadlock is declared, and one or
> the other is rolled back.
>
> It seems odd that a test suite would have this problem.  It looks like
> they give informative error messages though, that should be helpful.
>  Also, you can turn on query logging and look at pg_stat_activity to see
> what's up.
>
> Jenny Zhang wrote:
>
> >I am running OSDL-dbt1 - an e-commerce workload
> >(http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/)
> >against PostgreSQL: 7.3.3.  During the test, I saw a lot of messages in
> >the database log file:
> >
> >NOTICE:  _sc_id=0, _add_flag=0, _itemcount=0, _i_id=597, _pp_i_id=159,
> >c_id=32760
> >ERROR:  deadlock detected
> >WARNING:  Error occurred while executing PL/pgSQL function shopping_cart
> >WARNING:  line 311 at SQL statement
> >WARNING:  ROLLBACK: no transaction in progress
> >
> >The shopping_cart transaction does update/insert to shopping_cart and
> >shopping_cart_line tables, and if it is a new shopping_cart, it queries
> >the SEQUENCE scid to get a new shopping_cart.
> >
> >I tried to search the mailing list, and got the impression that if there
> >are foreign key related to the tables, then deadlock happens.  But that
> >is not my case.  I do not have foreign key either referring to this two
> >tables, or defined on those tables.
> >
> >Can anyone give some suggestions how I should analyze this problem?  Or
> >are there any documentation?  I am not driving the database very hard.
> >The only comparison I have are the previous runs I did against SAPDB,
> >which performs better.
> >
> >Let me know if you need more info.
> >
> >TIA,
> >
> >Jenny
> >
> >
> >------------------------------------------------------------------------
> >
> >SPAM: BAYES_00             (-5.2 points)  Bayesian classifier says spam probability is 0 to 1%
> >Score Total: -5.2
> >