|
Home > Archive > SQL Anywhere Feedback > January 2006 > dbremote -g default should be = 1
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
dbremote -g default should be = 1
|
|
| Andrei Iliev 2006-01-26, 4:56 pm |
| From the ASA documentation:
"-g options instructs the Message Agent to group transactions containing
less than n operations together with transactions that follow. The default
is twenty operations. Increasing the value of n can speed up processing of
incoming messages."
So, commit occurs only after 20 operations. If one of sql-operations fail
(duplicate PK, or something else) the whole bunch of operations will fail.
Most of the newbie do not know about this option and sooner or later face
up the situation I describe above.
I thinks default value should be more conservative and safe.
--
Andrei Iliev
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2006-01-26, 4:56 pm |
| A SQL error in one transaction will NEVER cause other transactions to fail
when SQL Remote is applying messages.
==
== Foreign Key Error Example
==
When a foreign key violation occurs when dbremote is applying grouped
transactions (the error occurs on COMMIT because wait_for_commit = true),
then we rollback the grouped transaction and replay the transactions a
second time, but this time we ungroup the transactions and use the original
COMMIT sequence from the sending side. You can see this in the following
SQL Remote output file. The 3rd insert (c_pkey = 502) into cons.child
should fail because the parent record ( p_pkey=101 in cons.parent) no longer
exists in the consolidated.
====
==== Table contents before dbremote runs
====
[0] [90] dbisql -nogui -c "dbn=cons" select * from cons.parent
Execution time: 0.016 seconds
p_pkey rep_id textcol
----------------------------
100 1 cons
(First 1 rows)
[0] [90] dbisql -nogui -c "dbn=cons" select * from cons.child
Execution time: 0.015 seconds
c_pkey p_pkey textcol
---------------------
(First 0 rows)
====
==== DBRemote output
====
I. 01/26 09:54:10. SQL Remote Message Agent Version 9.0.2.3247
[snip]
I. 01/26 09:54:10. Received message from "rem1" (0-0000365998-0000368402-0)
I. 01/26 09:54:10. Applying message from "rem1" (0-0000365998-0000368402-0)
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (500,100,'rem1')
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (501,100,'rem1')
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (502,101,'rem1')
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (503,100,'rem1')
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (504,100,'rem1')
I. 01/26 09:54:10. COMMIT
E. 01/26 09:54:10. SQL statement failed: (-194) No primary key value for
foreign key 'parent' in table 'child'
I. 01/26 09:54:10. ROLLBACK
I. 01/26 09:54:10. Applying message from "rem1" (0-0000365998-0000368402-0)
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (500,100,'rem1')
I. 01/26 09:54:10. COMMIT
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (501,100,'rem1')
I. 01/26 09:54:10. COMMIT
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (502,101,'rem1')
I. 01/26 09:54:10. COMMIT
E. 01/26 09:54:10. SQL statement failed: (-194) No primary key value for
foreign key 'parent' in table 'child'
I. 01/26 09:54:10. ROLLBACK
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (503,100,'rem1')
I. 01/26 09:54:10. COMMIT
I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
VALUES (504,100,'rem1')
I. 01/26 09:54:10. COMMIT
====
==== Table contents after dbremote runs
====
[0] [90] dbisql -nogui -c "dbn=cons" select * from cons.parent
Execution time: 0.016 seconds
p_pkey rep_id textcol
----------------------------
100 1 cons
(First 1 rows)
[0] [90] dbisql -nogui -c "dbn=cons" select * from cons.Child
Execution time: 0.016 seconds
c_pkey p_pkey textcol
---------------------
500 100 rem1
501 100 rem1
503 100 rem1
504 100 rem1
(First 4 rows)
==
== Primary Key Error Example
==
When an insert (or any other SQL error that is not a foreign key violation)
fails in a grouped transaction, because the error happens right away, we
simply skip the row and move on. In this example, it is the third row
(a_pkey=100) that will fail because the row already exists at the
consolidated.
====
==== Table contents before dbremote runs
====
[0] [90] dbisql -nogui -c "dbn=cons" select * from cons.admin
Execution time: 0.031 seconds
a_pkey textcol
---------------------
100 100_cons
(First 1 rows)
====
==== DBRemote output
====
I. 01/26 09:54:10. SQL Remote Message Agent Version 9.0.2.3247
[snip]
I. 01/26 09:47:41. Received message from "rem1" (0-0000365828-0000365998-0)
I. 01/26 09:47:41. Applying message from "rem1" (0-0000365828-0000365998-0)
I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
VALUES (98,'98_rem1')
I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
VALUES (99,'99_rem1')
I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
VALUES (100,'100_rem1')
E. 01/26 09:47:41. SQL statement failed: (-193) Primary key for table
'admin' is not unique
E. 01/26 09:47:41. Skipping:
E. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
VALUES (100,'100_rem1')
I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
VALUES (101,'101_rem1')
I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
VALUES (102,'102_rem1')
I. 01/26 09:47:41. COMMIT
====
==== Table contents after dbremote runs
====
[0] [90] dbisql -nogui -c "dbn=cons" select * from cons.admin
Execution time: 0.031 seconds
a_pkey textcol
---------------------
100 100_cons
98 98_rem1
99 99_rem1
101 101_rem1
102 102_rem1
(First 5 rows)
We ALWAYS ensure that if a SQL error occurs when applying operations, then
we will apply as much of the of the SQL as possible, regardless of whether
transactions are being grouped or not. If you can show me an example where
an error one transaction causes the data in another transaction to not be
applied, we want to see it, and we would want to fix it as soon as possible.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"
"Andrei Iliev" <xzxz@mail.ru> wrote in message
news:43d8bccd$1@foru
ms-1-dub...
> From the ASA documentation:
> "-g options instructs the Message Agent to group transactions
containing
> less than n operations together with transactions that follow. The default
> is twenty operations. Increasing the value of n can speed up processing of
> incoming messages."
>
> So, commit occurs only after 20 operations. If one of sql-operations fail
> (duplicate PK, or something else) the whole bunch of operations will fail.
> Most of the newbie do not know about this option and sooner or later face
> up the situation I describe above.
>
> I thinks default value should be more conservative and safe.
>
>
> --
> Andrei Iliev
>
>
| |
| Andrei Iliev 2006-01-27, 7:23 am |
| Dear Reg,
thank you very much for such amazing clarification.
The reson why I was confused is twofold:
1) In 1998 we have used ASA 5 and faced with the problem of missing
transaction in SQLremote enviroment. After digging the dbremote log we have
found that the cause of missing transaction was the problem i decribed ( if
one of sql-operations fail (duplicate PK, or something else) the whole bunch
of operations failed). After using -g 1 everything became ok. Later we saw
same problem with ASA 6 or 7. I dont know if it was bug or "feature" of that
version. Since then we are always use with option (-g 1 ). And I never
checked out behaivour of dbremote in newer version of ASA with - g > 1. I
apologize.
2) Strict sense reading documentation: "Message Agent group transactions
containing less than n operations together with transactions that follow." I
was completly sure that dbremote do something like that:
- apply slq statement 1
- apply slq statement 2
- apply slq statement 3
....
COMMIT
So, I thought, if COMMIT fail all sql will fail.
> We ALWAYS ensure that if a SQL error occurs when applying operations, then
> we will apply as much of the of the SQL as possible, regardless of whether
> transactions are being grouped or not.
I think this notes should be included in documentation.
Thanks again.
--
Andrei Iliev
"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:43d8e7f6$1@foru
ms-2-dub...
> A SQL error in one transaction will NEVER cause other transactions to fail
> when SQL Remote is applying messages.
>
> ==
> == Foreign Key Error Example
> ==
>
> When a foreign key violation occurs when dbremote is applying grouped
> transactions (the error occurs on COMMIT because wait_for_commit = true),
> then we rollback the grouped transaction and replay the transactions a
> second time, but this time we ungroup the transactions and use the
original
> COMMIT sequence from the sending side. You can see this in the following
> SQL Remote output file. The 3rd insert (c_pkey = 502) into cons.child
> should fail because the parent record ( p_pkey=101 in cons.parent) no
longer
> exists in the consolidated.
>
> ====
> ==== Table contents before dbremote runs
> ====
>
> [0] [90] dbisql -nogui -c "dbn=cons" select * from cons.parent
> Execution time: 0.016 seconds
> p_pkey rep_id textcol
> ----------------------------
> 100 1 cons
> (First 1 rows)
>
> [0] [90] dbisql -nogui -c "dbn=cons" select * from cons.child
> Execution time: 0.015 seconds
> c_pkey p_pkey textcol
> ---------------------
> (First 0 rows)
>
> ====
> ==== DBRemote output
> ====
>
> I. 01/26 09:54:10. SQL Remote Message Agent Version 9.0.2.3247
> [snip]
> I. 01/26 09:54:10. Received message from "rem1"
(0-0000365998-0000368402-0)
> I. 01/26 09:54:10. Applying message from "rem1"
(0-0000365998-0000368402-0)
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (500,100,'rem1')
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (501,100,'rem1')
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (502,101,'rem1')
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (503,100,'rem1')
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (504,100,'rem1')
> I. 01/26 09:54:10. COMMIT
> E. 01/26 09:54:10. SQL statement failed: (-194) No primary key value for
> foreign key 'parent' in table 'child'
> I. 01/26 09:54:10. ROLLBACK
> I. 01/26 09:54:10. Applying message from "rem1"
(0-0000365998-0000368402-0)
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (500,100,'rem1')
> I. 01/26 09:54:10. COMMIT
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (501,100,'rem1')
> I. 01/26 09:54:10. COMMIT
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (502,101,'rem1')
> I. 01/26 09:54:10. COMMIT
> E. 01/26 09:54:10. SQL statement failed: (-194) No primary key value for
> foreign key 'parent' in table 'child'
> I. 01/26 09:54:10. ROLLBACK
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (503,100,'rem1')
> I. 01/26 09:54:10. COMMIT
> I. 01/26 09:54:10. INSERT INTO cons. child(c_pkey,p_pkey,
textcol)
> VALUES (504,100,'rem1')
> I. 01/26 09:54:10. COMMIT
>
> ====
> ==== Table contents after dbremote runs
> ====
>
> [0] [90] dbisql -nogui -c "dbn=cons" select * from cons.parent
> Execution time: 0.016 seconds
> p_pkey rep_id textcol
> ----------------------------
> 100 1 cons
> (First 1 rows)
>
> [0] [90] dbisql -nogui -c "dbn=cons" select * from cons.Child
> Execution time: 0.016 seconds
> c_pkey p_pkey textcol
> ---------------------
> 500 100 rem1
> 501 100 rem1
> 503 100 rem1
> 504 100 rem1
> (First 4 rows)
>
> ==
> == Primary Key Error Example
> ==
>
> When an insert (or any other SQL error that is not a foreign key
violation)
> fails in a grouped transaction, because the error happens right away, we
> simply skip the row and move on. In this example, it is the third row
> (a_pkey=100) that will fail because the row already exists at the
> consolidated.
>
> ====
> ==== Table contents before dbremote runs
> ====
>
> [0] [90] dbisql -nogui -c "dbn=cons" select * from cons.admin
> Execution time: 0.031 seconds
> a_pkey textcol
> ---------------------
> 100 100_cons
>
> (First 1 rows)
>
> ====
> ==== DBRemote output
> ====
>
> I. 01/26 09:54:10. SQL Remote Message Agent Version 9.0.2.3247
> [snip]
> I. 01/26 09:47:41. Received message from "rem1"
(0-0000365828-0000365998-0)
> I. 01/26 09:47:41. Applying message from "rem1"
(0-0000365828-0000365998-0)
> I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
> VALUES (98,'98_rem1')
> I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
> VALUES (99,'99_rem1')
> I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
> VALUES (100,'100_rem1')
> E. 01/26 09:47:41. SQL statement failed: (-193) Primary key for table
> 'admin' is not unique
> E. 01/26 09:47:41. Skipping:
> E. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
> VALUES (100,'100_rem1')
> I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
> VALUES (101,'101_rem1')
> I. 01/26 09:47:41. INSERT INTO cons. admin(a_pkey,textcol
)
> VALUES (102,'102_rem1')
> I. 01/26 09:47:41. COMMIT
>
> ====
> ==== Table contents after dbremote runs
> ====
>
> [0] [90] dbisql -nogui -c "dbn=cons" select * from cons.admin
> Execution time: 0.031 seconds
> a_pkey textcol
> ---------------------
> 100 100_cons
> 98 98_rem1
> 99 99_rem1
> 101 101_rem1
> 102 102_rem1
>
> (First 5 rows)
>
> We ALWAYS ensure that if a SQL error occurs when applying operations, then
> we will apply as much of the of the SQL as possible, regardless of whether
> transactions are being grouped or not. If you can show me an example
where
> an error one transaction causes the data in another transaction to not be
> applied, we want to see it, and we would want to fix it as soon as
possible.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set filter to "Display ALL platforms IN ALL MONTHS"
>
>
> "Andrei Iliev" <xzxz@mail.ru> wrote in message
> news:43d8bccd$1@foru
ms-1-dub...
> containing
default[color=darkre
d]
of[color=darkred]
fail[color=darkred]
fail.[color=darkred]
face[color=darkred]
>
>
| |
| Pavel Karady 2006-01-27, 9:23 am |
| >> We ALWAYS ensure that if a SQL error occurs when applying operations,
>
> I think this notes should be included in documentation.
Very agreed!
Pavel
|
|
|
|
|