Hello everyone,
I have a critical issue with a stored procedure in a
transaction block in a high data load environment. I
use 2 postgresql database backends with sequoia 2.10.
The transaction has the following statements:
Begin Transaction
- get next sequence for Table 1 (this is not a
collable statement it is only a select from one of the
backends , the sequences are not overlapping on the 2
backends)
- get next sequence for Table 2
- get next sequence for Table 2
- get next sequence for Table 2
- Insert into Table 1
- Insert into Table 2
- Insert into Table 2
- Insert into Table 2
- callable statement for a Stored procedure that does
locks, inserts and updates on Table 3
Commit Transaction
If I run the same scenario without the stored
procedure everything works well so I think the problem
is related with the SP.
The scenario using the SP is ok if I only use
postgresql without sequoia.
I do not get any locks on postgresql in any scenario
(with or without sequoia).
The procedure must be executed on both backends
(because it updates the data in Table3). The backend
config for the stored procedure looks like:
<StoredProcedureSemantic procedureName="my_procedure"
parameterCount="1" writeTables="table3"
proceduresReferenced="" hasSelect="true"
hasInsert="true" hasUpdate="true" hasDelete="false"
hasDDL="false" hasTransaction="false"
isCausallyDependent="false" isCommutative="false" />
When running the performance test with 20 clients I
get the following errors in sequoia:
2007-04-25 12:46:56,829 DEBUG
controller.RequestManager.abrazo Read Stored Procedure
(158414) : {call my_procedure(?)}/<!%...
2007-04-25 12:46:56,829 DEBUG
sequoia.controller.loadbalancer Request 158414 execute
on 2 backends
2007-04-25 12:47:40,084 DEBUG
sequoia.controller.loadbalancer Building wait-for
graph...
2007-04-25 12:47:40,084 DEBUG
sequoia.controller.loadbalancer table1 locked by 47929
2007-04-25 12:47:40,087 DEBUG
sequoia.controller.loadbalancer table2 locked by 47929
2007-04-25 12:47:40,088 DEBUG
sequoia.controller.loadbalancer Walking wait-for
graph...
2007-04-25 12:47:40,090 DEBUG
sequoia.controller.loadbalancer 47968 waits for table1
locked by 47929
2007-04-25 12:47:40,090 DEBUG
sequoia.controller.loadbalancer 47968 waits for table2
locked by 47929
2007-04-25 12:47:40,090 DEBUG
sequoia.controller.loadbalancer 47933 waits for table1
locked by 47929
2007-04-25 12:47:40,090 DEBUG
sequoia.controller.loadbalancer 47933 waits for table2
locked by 47929
and so on for all other 19 or so processes
Do you have any clue how this could be fixed or what
work around can help with my problem?
Thanks a lot,
Ioana
Ask a question on any topic and get answers from real people. Go to
Yahoo! Answers and share what you know at http://ca.answers.yahoo.com
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
