Hi all,
Here are the details on how this issue occurs:
There are 2 clients calling the same transaction block
in the same time:
BEGIN
Insert Into Table1
Update Table2
Insert Into Table 1
COMMIT
The execution of the sql statements is happening in
the following order:
Client 1 - Insert Into Table1
Client 2 - Insert Into Table1
Client 2 - Update Table2
Client 1 - Update Table2 - this is the same record
updated by Client 2 - Update Table2 - so it is gonna
be locked until the transaction for Client 2 is
committed
Client 2 - Insert Into Table 1 - This is where the
problem starts because this is locked by Client 1 and
it shouldn't be. It is an insert on a new record.
At this point I get the following error in sequoia:
2006-09-29 10:13:20,496 INFO
sequoia.controller.loadbalancer Deadlock detected on
backend abrazo1, aborting transaction 15
2006-09-29 10:13:20,501 ERROR
controller.loadbalancer.RAIDb1 write request 49
failed:
Backend abrazo - BackendWorkerThread for backend
'abrazo1' with RAIDb level:1 failed (Transaction
aborted due to deadlock)
2006-09-29 10:13:20,511 INFO
backend.DatabaseBackend.abrazo1 Backend Backend:
Name[abrazo1] State[1] JDBCConnected[true]
ActiveTransactions [2]
PersistentConnections[{}] PendingRequests[0] already
notified task StatementExecuteUpdateTask from
transaction 15 (update ioanatab3 set field1 =
1 where field1 = 1/)
2006-09-29 10:13:20,513 INFO sequoia.controller.cache
Flushing whole cache due to rollback of transaction :
15
2006-09-29 10:13:20,514 ERROR
sequoia.controller.connection Cannot find connection
for transaction 15
2006-09-29 10:13:20,515 FATAL
backend.DatabaseBackend.abrazo1 Runtime error while
executing task: RollbackTask (15)
java.lang.IllegalArgumentException: Transaction id 15
is not started on backend abrazo1
at
org.continuent.sequoia.controller.backend.DatabaseBackend.stopTransaction(DatabaseBackend.java:1106)
at
org.continuent.sequoia.controller.loadbalancer.tasks.RollbackTask.executeTask(RollbackTask.java:172)
at
org.continuent.sequoia.controller.loadbalancer.tasks.AbstractTask.execute(AbstractTask.java:140)
at
org.continuent.sequoia.controller.loadbalancer.BackendWorkerThread.run(BackendWorkerThread.java:194)
2006-09-29 10:13:20,516 ERROR
controller.loadbalancer.RAIDb1 Rollback of transaction
15 failed:
Backend abrazo - BackendWorkerThread for backend
'abrazo1' with RAIDb level:1 failed (Transaction id 15
is not started on backend abrazo1)
2006-09-29 10:13:20,516 INFO
controller.virtualdatabase.abrazo Abort after request
failure in transaction did not succeed probably
because the transaction has already been
aborted
java.sql.SQLException: write request 49 failed:
Backend abrazo - BackendWorkerThread for backend
'abrazo1' with RAIDb level:1 failed (Transaction
aborted due to deadlock)
at
org.continuent.sequoia.common.exceptions.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:88)
at
org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.checkTaskCompletion(RAIDb1.java:938)
at
org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.execWriteRequest(RAIDb1.java:343)
at
org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.statementExecuteUpdate(RAIDb1.java:249)
at
org.continuent.sequoia.controller.requestmanager.RequestManager.loadBalanceStatementExecuteUpdate(RequestManager.java:913)
at
org.continuent.sequoia.controller.requestmanager.RequestManager.statementExecuteUpdate(RequestManager.java:662)
at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabase.statementExecuteUpdate(VirtualDatabase.java:609)
at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabaseWorkerThread.statementExecuteUpdate(VirtualDatabaseWorkerThread.
java:2175)
at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabaseWorkerThread.run(VirtualDatabaseWorkerThread.java:421)
2006-09-29 10:13:20,517 WARN
controller.virtualdatabase.abrazo Request update
ioanatab3 set field1 = 1 where fi... failed,
transaction has been aborted (write request 49
failed:
Backend abrazo - BackendWorkerThread for backend
'abrazo1' with RAIDb level:1 failed (Transaction
aborted due to deadlock)
)
2006-09-29 10:13:20,518 WARN
virtualdatabase.VirtualDatabaseWorkerThread.abrazo
Error during command execution (write request 49
failed:
Backend abrazo - BackendWorkerThread for backend
'abrazo1' with RAIDb level:1 failed (Transaction
aborted due to deadlock)
)
When the same test I run without the update statement,
in the same order everything works well with no locks.
Also the same test works fine if the application is
connected directly to postgresql.
Another think, I used sequoia with only one backend
for this test.
Thank you,
Ioana
--- Julio Leyva <[EMAIL PROTECTED]> wrote:
>
> Hi Oliver
>
> Actually these are the messages when we ran the test
> with 2 backends
> (postgresql 8.14)
>
>
> 2006-09-28 14:25:00,796 INFO
> sequoia.controller.loadbalancer Deadlock
> detected on backend testdb2, aborting transaction
> 13952628
> 2006-09-28 14:25:00,799 ERROR
> controller.loadbalancer.RAIDb1 write request
> 27947321 failed:
> Backend testdb - BackendWorkerThread for backend
> 'testdb1' with RAIDb
> level:1 failed (Transaction aborted due to deadlock)
> Backend testdb - BackendWorkerThread for backend
> 'testdb2' with RAIDb
> level:1 failed (Transaction aborted due to deadlock)
>
> 2006-09-28 14:25:00,800 INFO
> sequoia.controller.cache Flushing whole cache
> due to rollback of transaction : 13952628
> 2006-09-28 14:25:00,800 WARN
> controller.virtualdatabase.abrazo Request
> INSERT INTO table1 (TRANSACTIONI... failed,
> transaction has been aborted
> (write request 27947321 failed:
> Backend testdb - BackendWorkerThread for backend
> 'testdb1' with RAIDb
> level:1 failed (Transaction aborted due to deadlock)
> Backend abrazo - BackendWorkerThread for backend
> 'testdb2' with RAIDb
> level:1 failed (Transaction aborted due to deadlock)
> )
> 2006-09-28 14:25:00,800 WARN
> virtualdatabase.VirtualDatabaseWorkerThread.testdb
> Error during command
> execution (write request 27947321 failed:
> Backend testdb - BackendWorkerThread for backend
> 'testdb1' with RAIDb
> level:1 failed (Transaction aborted due to deadlock)
> Backend testdb - BackendWorkerThread for backend
> 'testdb2' with RAIDb
> level:1 failed (Transaction aborted due to deadlock)
> )
> 2006-09-28 14:25:00,805 WARN
> backend.DatabaseBackend.testdb1 No transaction
> medatada found for transaction 13952628 releasing
> locks manually
> 2006-09-28 14:25:00,807 WARN
> backend.DatabaseBackend.testdb2 No transaction
> medatada found for transaction 13952628 releasing
>
> So The messages in the previous e-mail is when we
> ran the test just with
> one backend
>
> The test still run, but is extremly slow with
> sequoia, I believe because of
> the deadlock, however when we run the aplication
> without sequoia there is no
> deadlock reported by postgresql.
>
>
>
> Thanks
>
>
>
> >From: Olivier Fambon
> <[EMAIL PROTECTED]>
> >Reply-To: Sequoia general mailing list
> <[EMAIL PROTECTED]>
> >To: Sequoia general mailing list
> <[EMAIL PROTECTED]>
> >Subject: Re: [Sequoia] Sequoia 1.10 error messages
> >Date: Fri, 29 Sep 2006 10:08:37 +0200
> >
> >Julio Leyva wrote [09/28/2006 11:23 PM]:
> >>Hi all
> >>
> >
> >Hi Julio,
> >
> >>We are testing an aplication on a volumen test bed
> using sequoia 2.10 and
> >>one backend postgresql 8.14
> >>
> >>We are seeing these error messages when we try to
> update or insert info in
> >>our tables.
> >>
> >>
> >>2006-09-28 16:55:05,315 DEBUG
> controller.RequestManager.abrazo Load
> >>balancing write request (21006,6042): INSERT INTO
> table2(TRANSACTIONI...
> >>2006-09-28 16:55:05,316 DEBUG
> controller.RequestManager.abrazo
> >>Invalidating cache for write request (21006):
> INSERT INTO table2
> >>(TRANSACTIONI...
> >>
> >>2006-09-28 16:55:05,502 DEBUG
> controller.RequestManager.abrazo Load
> >>balancing write request (21009,6043): Update
> table1 set current_...
> >>2006-09-28 16:55:05,509 DEBUG
> controller.RequestManager.abrazo
> >>Invalidating cache for write request (21009):
> Update table1 current_...
> >>
> >>
> >>So we ran the same test just without sequoia and
> it works ok.
> >>
> >>any ideas?
> >>
> >
> >These are debug messages, not error messages.
> >
> >Does your test actually fail ?
> >
> >_______________________________________________
> >Sequoia mailing list
> >[EMAIL PROTECTED]
>
>https://forge.continuent.org/mailman/listinfo/sequoia
>
>
>
> _______________________________________________
> Sequoia mailing list
> [EMAIL PROTECTED]
>
https://forge.continuent.org/mailman/listinfo/sequoia
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
_______________________________________________
Sequoia mailing list
[EMAIL PROTECTED]
https://forge.continuent.org/mailman/listinfo/sequoia
