From: Jenny Z. <je...@os...> - 2003-12-19 00:50:50
|
Ian, On Wed, 2003-12-17 at 07:48, Ian Harding wrote: > The thing I see that is scary to me is <IDLE> in transaction. That > means that your application has opened a transaction and is sitting > around with it open. That is always a bad idea. Another thing that > makes me think the problem is in the application is the > Before I fixed the bug, all my database connections were doing shopping_cart. After the bug fix, most of them were idle except several doing shopping_cart. I think that means I did not drive the database hard enough, or I have too many connections open. Is that right? > WARNING: ROLLBACK: no transaction in progress > > in the error log. The application issued a rollback after PostgreSQL > had already rolled back the transaction. > > I suspect something like this is happening. The application begins a > transaction. It calls shopping_cart to purchase each item. Shopping > cart updates the "on-hand"quantity. The application then issues a > commit. That is not a sequence of events that will tend to give decent > concurrency. > If my shopping cart has item X and I want to add item Y , and yours has > item Y and you want to add item X to it, I will lock X, then try to lock > Y, meanwhile, you will have locked Y and try to lock X. > > Of course, I don't know why this would have worked with any other > database either, but the functions would have needed to be substantially > re-written to work on PostgreSQL, so functionality like nested > transactions would have to have been written out. > > The application may have been counting on that functionality to exist. > In the above example, the shopping_cart function could have been > written to retry until success (or timeout) in a database that supports > nested transactions. > > Good luck! > Only that pgsql does not support nested transactions. The whole pl/pgsql function is a transaction(even if I commit in the middle of it) and the locks are only released after the whole transaction. You are right about the error messages. In the stored procedure, I added BEGIN/commit, and in the libpq code, I checked the return code and decide if I want to commit/roll back. That is bad. I will fix the bug. Thanks for your help, I really appreciated your input. Jenny > > Jenny Zhang wrote: > > >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 > >>> > >>> > >>> > >>>------------------------------------------------------------------------ > >>> > >>>SPAM: BAYES_01 (-5.4 points) Bayesian classifier says spam probability is 1 to 10% > >>>Score Total: -5.4 > >>> |