True or False?
It is a fundamental rule of The MobiLink Synchronization Doctrine that uploaded changes are processed as a single all-or-nothing transaction on the consolidated database.
Here's a clue:
"The MobiLink server incorporates changes uploaded from each MobiLink client into the consolidated database in one transaction. It commits these changes once it has completed inserting new rows, deleting old rows, making updates, and resolving any conflicts."- Transactions in the synchronization process
OK, true or false?
No... the correct answer is "False"
It was "True" for many years, but... did you know that since version 9.0.1 of SQL Anywhere it has been possible to break the one-transaction-rule for the upload stream?
"When you use -tu, you create a transactional upload: dbmlsync uploads each transaction on the remote database as a distinct transaction. The MobiLink server applies and commits each transaction separately when it is received."- dbmlsync -tu option
It
*is* well documented, but not easy to find, perhaps testimony to
the stealth nature of SQL Anywhere.
If you're familiar with
replication via SQL Remote or
Replication Server, transactional upload is similar to how uploads work with
dbremote.exe (the SQL Remote client) and
dbltm.exe (the Log Transfer Manager, also known as... deep breath... the SQL Anywhere Replication Agent for Sybase Replication Server). Some folks have designed their systems to expect uploads to be applied to the consolidated database in the same order as the original transactions were applied to the remote databases. With the
end of support for SQL Remote for ASE and
for the Log Transfer Manager altogether,
MobiLink is now pretty much the only game in town if you're
not using SQL Anywhere for the consolidated database.
But wait, there's more...
- MobiLink transactional upload may help with performance if the consolidated database is happier with small transactions than a giant all-or-nothing upload transaction.
- Transactional upload may also help with robustness by allowing part of an upload to succeed before a failure occurs on an unreliable communication line between the MobiLink client and server.
- The mlsrv12 -tx n option tells the MobiLink server to batch groups of n transactions and commit them together, when you use the dbmlsync -tu option on the MobiLink client side to get transactional upload.
- Also note: The dbmlsync inc (increment) extended option is an alternative to dbmlsync -tu. When you specify inc=1M, for example, the single all-or-nothing upload transaction is broken into 1M (very approximate) chunks and each chunk is committed on the consolidated database. This isn't the same as transactional upload, not even close, but like transactional upload it might help with performance and robustness.
The Dark Side of Transactional Upload
MobiLink transactional upload ain't
exactly the same as how SQL Remote and Replication Server work: transaction order isn't exactly the same as operation order. MobiLink still uploads full row images rather than column-level changes as they were recorded in the transaction log, and multiple operations on the same row in the same transaction are effectively "coalesced" (combined) into the final row image that MobiLink uploads.
Who cares? Well, in most cases, you don't. Most folks don't need transactional upload to make their systems work properly; the only reason they might use it is for performance and/or robustness.
Of the remainder (folks who need transactional upload), most don't care about transaction-versus-operation-order; as long as the end result of each transaction is the same on the remote and consolidated databases, they're happy.
Who's left? Folks who need transactional upload, but can't use it without changing the way their system works because they really depend on the order of operations within a transaction.
The following end-to-end demonstration uses SQL Anywhere 11 for the remote database and Microsoft SQL Server 2008 for the consolidated database
- to show how MobiLink transactional upload works, and
- to demonstrate how the difference between transaction order and operation order might affect the consolidated database.
The phrase "end-to-end demonstration" in the previous section means just that. The code makes no assumptions except
- that you have a Microsoft SQL Server 2008 server up and running, and
- that you have a working copy of SQL Anywhere 11 installed.
The code does
not assume you have any test databases; it starts from scratch with CREATE DATABASE and dbinit.exe.
Having said that, not every line of code is explained here, just the pertinent parts. Feel free to skip over the parts you don't care about, and if you don't like the verbosity please pay close attention to what Alice has to say:
Step 1: Create and set up the consolidated database.
1_create_and_set_up_cons.bat
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe"^
-d master^
-i "s1_create_and_set_up_cons.sql"^
-I^
-P j68Fje9#fyu489^
-S localhost^
-U sa
PAUSE
s1_create_and_set_up_cons.sql
---------------------------------------------------------------------
-- Create separate database "mltest" (change the paths as desired).
USE master
GO
CREATE DATABASE mltest
ON
( NAME = mltest,
FILENAME = 'D:\data\mltest\mltest.mdf',
SIZE = 100MB )
LOG ON
( NAME = mltest_log,
FILENAME = 'D:\data\mltest\mltest.ldf',
SIZE = 100MB )
GO
---------------------------------------------------------------------
USE mltest
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------
CREATE TABLE account_balance (
account_number VARCHAR ( 10 ) NOT NULL,
amount DECIMAL ( 15, 2 ) NOT NULL DEFAULT 0,
limit_exceeded VARCHAR ( 1 ) NOT NULL DEFAULT 'N' CHECK ( limit_exceeded IN ( 'Y', 'N' ) ),
PRIMARY KEY ( account_number ) )
GO
---------------------------------------------------------------------
BEGIN TRANSACTION
INSERT account_balance ( account_number ) VALUES ( '100' )
INSERT account_balance ( account_number ) VALUES ( '200' )
INSERT account_balance ( account_number ) VALUES ( '300' )
INSERT account_balance ( account_number ) VALUES ( '400' )
INSERT account_balance ( account_number ) VALUES ( '500' )
INSERT account_balance ( account_number ) VALUES ( '600' )
INSERT account_balance ( account_number ) VALUES ( '700' )
INSERT account_balance ( account_number ) VALUES ( '800' )
INSERT account_balance ( account_number ) VALUES ( '900' )
COMMIT TRANSACTION
GO
---------------------------------------------------------------------
CREATE TRIGGER tru_account_balance ON account_balance AFTER INSERT, UPDATE
AS
BEGIN
-- Flag the row as soon as it exceeds the limit.
UPDATE account_balance
SET account_balance.limit_exceeded = 'Y'
FROM inserted
WHERE account_balance.account_number = inserted.account_number
AND account_balance.limit_exceeded = 'N'
AND account_balance.amount > 5000.00
END
GO
The CREATE TABLE supports one application requirement: Store the current balance for each account.
The INSERT statements set up accounts 100 through 900 to be used in examples that will be numbered 1 through 9.
The CREATE TRIGGER supports another application requirement: Flag each row as soon as the balance exceeds $5000, and preserve that flag setting even if the balance drops to $5000 or lower at a later point.
Step 2: Exercise the trigger.
2_update_cons_to_demonstrate_trigger.bat
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe"^
-d mltest^
-i "s2_update_cons_to_demonstrate_trigger.sql"^
-I^
-P j68Fje9#fyu489^
-S localhost^
-U sa
PAUSE
s2_update_cons_to_demonstrate_trigger.sql
-- Example 1: One local transaction, one operation.
BEGIN TRANSACTION
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '100'
COMMIT TRANSACTION
GO
-- Example 2: One local transaction, two operations.
BEGIN TRANSACTION
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '200'
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '200'
COMMIT TRANSACTION
GO
-- Example 3: Two local transactions, one operation each.
BEGIN TRANSACTION
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '300'
COMMIT TRANSACTION
GO
BEGIN TRANSACTION
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '300'
COMMIT TRANSACTION
GO
The script above contains three examples:
- Example 1 performs one single operation in a single transaction to cause the account balance to exceed $5000 and remain there.
- Example 2 performs two operations in a single transaction to cause the account balance to exceed $5000 and then fall back down to $5000.
- Example 3 does the same thing, but it uses a separate transaction for each operation.
Step 3: Show what the trigger did.
3_display_cons.bat
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe"^
-d mltest^
-e^
-q "SELECT * FROM account_balance ORDER BY account_number"^
-I^
-P j68Fje9#fyu489^
-S localhost^
-U sa
In all three examples the flag should be set to 'Y' and left there... and the consolidated database confirms that:
SELECT * FROM account_balance ORDER BY account_number
account_number amount limit_exceeded
-------------- ----------------- --------------
100 6000.00 Y
200 5000.00 Y
300 5000.00 Y
400 .00 N
500 .00 N
600 .00 N
700 .00 N
800 .00 N
900 .00 N
(9 rows affected)
1>
Step 4: Add MobiLink support for SQL Server to the consolidated database.
4_run_syncmss_on_cons.bat
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe"^
-d mltest^
-i "%SQLANY11%\MobiLink\setup\syncmss.sql"^
-I^
-P j68Fje9#fyu489^
-S localhost^
-U sa
PAUSE
Step 5: Create the MobiLink event scripts on the consolidated database.
5_set_up_MobiLink_scripts_on_cons.bat
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe"^
-d mltest^
-i "s5_set_up_MobiLink_scripts_on_cons.sql"^
-I^
-P j68Fje9#fyu489^
-S localhost^
-U sa
PAUSE
s5_set_up_MobiLink_scripts_on_cons.sql
USE mltest
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------
EXECUTE ml_add_column 'v1', 'account_balance', NULL, NULL
GO
EXECUTE ml_add_column 'v1', 'account_balance', 'account_number', NULL
GO
EXECUTE ml_add_column 'v1', 'account_balance', 'amount', NULL
GO
---------------------------------------------------------------------
EXECUTE ml_add_table_script 'v1', 'account_balance', 'upload_insert', NULL
GO
EXECUTE ml_add_table_script 'v1', 'account_balance', 'upload_insert', '
INSERT account_balance
( account_number,
amount )
VALUES ( {ml r.account_number},
{ml r.amount} )'
GO
---------------------------------------------------------------------
EXECUTE ml_add_table_script 'v1', 'account_balance', 'upload_update', NULL
GO
EXECUTE ml_add_table_script 'v1', 'account_balance', 'upload_update', '
UPDATE account_balance
SET amount = {ml r.amount}
WHERE account_number = {ml r.account_number}'
GO
---------------------------------------------------------------------
EXECUTE ml_add_table_script 'v1', 'account_balance', 'upload_delete', NULL
GO
EXECUTE ml_add_table_script 'v1', 'account_balance', 'upload_delete', '
DELETE account_balance
WHERE account_number = {ml r.account_number}'
GO
The SQL Server EXECUTE statements set up ordinary MobiLink event scripts for handling INSERT, UPDATE and DELETE operations that have been uploaded from the remote to the consolidated database.
Step 6: Set up the remote database.
6_create_and_set_up_remo.bat
"%SQLANY11%\bin32\dbinit.exe"^
remo.db
"%SQLANY11%\bin32\\dbspawn.exe"^
-f "%SQLANY11%\bin32\dbeng11.exe"^
-o dbeng11_log_remo.txt^
-x tcpip^
remo.db
"%SQLANY11%\bin32\dbisql.com"^
-c "ENG=remo;DBN=remo;UID=dba;PWD=sql"^
READ ENCODING Cp1252 "s6_create_and_set_up_remo.sql"
PAUSE
s6_create_and_set_up_remo.sql
CREATE TABLE account_balance (
account_number VARCHAR ( 10 ) NOT NULL,
amount DECIMAL ( 15, 2 ) NOT NULL DEFAULT 0,
PRIMARY KEY ( account_number ) )
GO
---------------------------------------------------------------------
INSERT account_balance ( account_number ) VALUES ( '400' );
INSERT account_balance ( account_number ) VALUES ( '500' );
INSERT account_balance ( account_number ) VALUES ( '600' );
INSERT account_balance ( account_number ) VALUES ( '700' );
INSERT account_balance ( account_number ) VALUES ( '800' );
INSERT account_balance ( account_number ) VALUES ( '900' );
COMMIT;
---------------------------------------------------------------------
CREATE PUBLICATION p1 (
TABLE account_balance ( account_number,
amount ) );
CREATE SYNCHRONIZATION USER "1" TYPE tcpip;
CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1";
The script above creates the account_balance table in the remote database and inserts 6 rows to exactly match the corresponding 6 rows in the consolidated database, except for the fact that the limit_exceeded column does not exist on the remote database.
The remaining CREATE statements serve to define how the remote database will be synchronized with the consolidated database, as well as defining the point in time after which changes will be processed by the first synchronization. In other words, the earlier INSERT operations won't be uploaded, but any subsequent changes will be.
Step 7: Update the remote database to demonstrate MobiLink "Classic" mode.
7_update_remo_to_demonstrate_dbmlsync_without_tu.bat
"%SQLANY11%\bin32\dbisql.com"^
-c "ENG=remo;DBN=remo;UID=dba;PWD=sql"^
READ ENCODING Cp1252 "s7_update_remo_to_demonstrate_dbmlsync_without_tu.sql"
PAUSE
s7_update_remo_to_demonstrate_dbmlsync_without_tu.sql
-- Example 4: One synchronization without -tu, one transaction, one operation.
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '400';
COMMIT;
-- Example 5: One synchronization without -tu, one transaction, two operations.
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '500';
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '500';
COMMIT;
-- Example 6: One synchronization without -tu, two transactions, one operation each.
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '600';
COMMIT;
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '600';
COMMIT;
SELECT * FROM account_balance ORDER BY account_number;
The script above repeats the three examples from earlier, except this time the updates are performed on the remote database:
- Example 4 performs one single operation in a single transaction to cause the account balance to exceed $5000 and remain there.
- Example 5 performs two operations in a single transaction to cause the account balance to exceed $5000 and then fall back down to $5000.
- Example 6 does the same thing, but it uses a separate transaction for each operation.
Here's what the remote database looks like at this point:
account_number amount
--------------------------------
400 6000.00
500 5000.00
600 5000.00
700 0.00
800 0.00
900 0.00
(6 rows)
Step 8: Start the MobiLink server on the consolidated database.
8_run_mlsrv11.bat
"%SQLANY11%\bin32\mlsrv11.exe"^
-c "DSN=mltest;UID=sa;PWD=j68Fje9#fyu489"^
-fr^
-o mlsrv11_log_cons.txt^
-os 10M^
-ppv 60^
-vcefhimnopqrstu^
-zu+
Step 9: Run a synchronization session in MobiLink "Classic" mode.
9_run_dbmlsync_without_tu.bat
"%SQLANY11%\bin32\dbmlsync.exe"^
-c "ENG=remo;DBN=remo;UID=dba;PWD=sql"^
-e "adr='host=localhost';sv=v1"^
-o dbmlsync_log_remo_from_step_9.txt^
-os 10M^
-uo^
-vnorsu^
-x
You can run 3_display_cons.bat again to see what happened during the MobiLink "Classic" mode upload above, and the news is not good: Only one of the three uploaded rows 400, 500 and 600 has limit_exceeded set to 'Y' even though all three rows exceeded $5000 at one point or another before they were uploaded.
SELECT * FROM account_balance ORDER BY account_number
account_number amount limit_exceeded
-------------- ----------------- --------------
100 6000.00 Y
200 5000.00 Y
300 5000.00 Y
400 6000.00 Y
500 5000.00 N
600 5000.00 N
700 .00 N
800 .00 N
900 .00 N
(9 rows affected)
1>
That's what happens when you're not using transactional upload: MobiLink only sends the final version of each updated row, and so when the trigger fired on the consolidated database it did not set the flag for the two rows where the final amount was $5000.
Step 10: Update the remote database to demonstrate transactional upload.
10_update_remo_to_demonstrate_dbmlsync_tu.bat
"%SQLANY11%\bin32\dbisql.com"^
-c "ENG=remo;DBN=remo;UID=dba;PWD=sql"^
READ ENCODING Cp1252 "s10_update_remo_to_demonstrate_dbmlsync_tu.sql"
PAUSE
s10_update_remo_to_demonstrate_dbmlsync_tu.sql
-- Example 7: One synchronization with -tu, one transaction, one operation.
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '700';
COMMIT;
-- Example 8: One synchronization with -tu, one transaction, two operations.
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '800';
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '800';
COMMIT;
-- Example 9: One synchronization with -tu, two transactions, one operation each.
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '900';
COMMIT;
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '900';
COMMIT;
SELECT * FROM account_balance ORDER BY account_number;
The script above repeats the same three examples yet again:
- Example 7 performs one single operation in a single transaction to cause the account balance to exceed $5000 and remain there.
- Example 8 performs two operations in a single transaction to cause the account balance to exceed $5000 and then fall back down to $5000.
- Example 9 does the same thing, but it uses a separate transaction for each operation.
Here's what the remote database looks like at this point:
account_number amount
--------------------------------
400 6000.00
500 5000.00
600 5000.00
700 6000.00
800 5000.00
900 5000.00
(6 rows)
Step 11: Run a synchronization session using transactional upload.
11_run_dbmlsync_with_tu.bat
"%SQLANY11%\bin32\dbmlsync.exe"^
-c "ENG=remo;DBN=remo;UID=dba;PWD=sql"^
-e "adr='host=localhost';sv=v1"^
-o dbmlsync_log_remo_from_step_11.txt^
-os 10M^
-tu^
-uo^
-vnorsu^
-x
The news on the consolidated database is a bit better when transactional upload is used: Two of the three uploaded rows 700, 800 and 900 have limit_exceeded set to 'Y'. However, two updates on row 800 were performed in one single transaction on the remote database, and MobiLink combines all the changes made to one row in one transaction even though transactional upload is used, so... when the trigger fires for that row, it only fires once, and it sees the final value $5000, so limit_exceeded remains 'N'.
SELECT * FROM account_balance ORDER BY account_number
account_number amount limit_exceeded
-------------- ----------------- --------------
100 6000.00 Y
200 5000.00 Y
300 5000.00 Y
400 6000.00 Y
500 5000.00 N
600 5000.00 N
700 6000.00 Y
800 5000.00 N
900 5000.00 Y
(9 rows affected)
1>
The situation is better for row 900 (limit_exceeded is set to 'Y') because the two updates were done in separate transactions, and that's how you have to change your application if you don't want operations combined when they are uploaded: you have to put different operations on the same row in different transactions.
The next two steps perform cleanup when you're done, or when you want to start the demonstration over from scratch.
Step 12: Cleanup - Drop the consolidated database
12_drop_cons.bat
PAUSE Are you sure you want to drop the consolidated database?
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe"^
-d master^
-i "s12_drop_cons.sql"^
-I^
-P j68Fje9#fyu489^
-S localhost^
-U sa
PAUSE
s12_drop_cons.sql
USE master
GO
BEGIN TRY
DROP DATABASE mltest
END TRY
BEGIN CATCH
-- ignore error
END CATCH
GO
Step 13: Cleanup - Stop and drop the remote database.
13_stop_and_drop_remo.bat
PAUSE Are you sure you want to delete all the files?
"%SQLANY11%\bin32\dbstop.exe" -c "ENG=remo;DBN=remo;UID=dba;PWD=sql" -y
PAUSE Wait a bit, then
ERASE /F *.log
ERASE /F dbeng11_log_remo.txt
ERASE /F dbmlsync_log_remo*.txt
ERASE /F mlsrv.mle
ERASE /F mlsrv11_log_cons.txt
ERASE /F remo.db
ERASE /F remo.rid
PAUSE
