deadlock detected when inserting data

[Jenny Zhang]

I am running OSDL-dbt1 - an e-commerce workload
(http://www.osdl.org/lab_activities/k...te/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
--
Jenny Zhang
Open Source Development Lab
12725 SW Millikan Way, Suite 400
Beaverton, OR 97005
[phone] ext 31

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jenny Zhang]

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...2/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/k...te/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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Oliver Elphick]

On Mon, 2003-12-15 at 22:43, Jenny Zhang wrote:

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...2/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?

It is true. If no transaction is already in progress, each statement is
a transaction in itself (or begins a transaction if autocommit is off).
Therefore any function must necessarily execute inside a transaction.

You cannot use BEGIN in a PL/pgSQL function to begin a transaction,
since a transaction is already in progress. In PL/pgSQL, BEGIN marks
the start of the executed code (and follows any declarations).

--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The fear of the LORD is the instruction of wisdom, and
before honour is humility." Proverbs 15:33

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings