Ensuring no rows missed with timestamp-based downloads and Oracle consolidated databases
Graham Hurst
consolidated database, you need to take steps to ensure that all changes
will be downloaded. Otherwise uncommitted changes from before a download
may be missed in downloads that occur after the changes are committed.
This is because of Oracle's implementation of read consistency, often
referred to as "snapshot isolation" or "non-blocking reads". (In other
RDBMSes the download will block until changes are committed, assuming
MobiLink's default isolation level, so changes are not missed.)
As of 9.0.2 build 3021, for Oracle consolidated databases the MobiLink
server will set the next "last download time" to the start time of the
oldest open transaction. This ensures that all changes will be picked up
in the next synchronization that occurs after they are committed. The
Oracle account used by the MobiLink server must have permission for the
V_$TRANSACTION Oracle system view. Note that only SYS can grant this
access. The Oracle syntax is:
grant select on SYS.V_$TRANSACTION to <user_name>;
If you are using timestamp-based synchronization with Oracle and have
long-running transactions, then this change will increase the chance of
the same changes being downloaded more than once. MobiLink
synchronization efficiently handles such duplication, but the
duplication may affect performance, particularly if some transactions
are longer than the time between synchronizations.
You can override this behaviour by using the
modify_next_last_download_timestamp event.
For earlier versions of the MobiLink server, you can obtain equivalent
functionality with the following procedure (labelled A, with
alternatives B, C and D following):
A. The simplest way to make sure that all changes are downloaded,
assuming you are using a trigger to maintain a last_modified marker for
each row, is to change the next "last download time" to be the earliest
start time of the open transactions. MobiLink offers the
modify_next_last_download_timestamp connection event for modifying the
next "last download time", but it is invoked at the end of the download
transaction, and you want to determine the new value at the beginning of
the download transaction. So you need to determine the value in the
begin_download connection event, and use it in the
modify_next_last_download_timestamp event. In this case the value is
stored in a package variable between the events. Here are detailed
instructions:
1. Ensure that the MobiLink connection (i.e. Oracle user) has permission
to access the V$TRANSACTION Oracle system view. Only SYS can grant this
access, and it must be granted for the base V_$TRANSACTION view
(V$TRANSACTION is a synonym for the V_$TRANSACTION view, and hence
cannot have permission granted on it directly):
grant select on SYS.V_$TRANSACTION to <user_name>;
It is essential for the user to have the permission directly, rather
than through a role, because a stored procedure does not have role
privileges.
If you do not want to grant access to the whole view, you could instead
define a view that only allows access to the required start_time column,
as in the following example:
create view transaction_start_times
as
select start_time from v$transaction;
grant select on transaction_start_times to public;
2. Create a package with a variable to hold the timestamp value (use
DATE type instead of TIMESTAMP for Oracle 8), and procedures to set and
get it (from MobiLink events):
create or replace package SyncVars
as
procedure SetDownloadTimestamp;
function GetDownloadTimestamp return timestamp;
end SyncVars;
/
create or replace package body SyncVars
as
DownloadTimestamp timestamp;
procedure SetDownloadTimestamp
as
begin
select nvl( min( to_timestamp( start_time, 'mm/dd/rr hh24:mi:ss' ) ),
localtimestamp )
into DownloadTimestamp
from v$transaction;
end SetDownloadTimestamp;
function GetDownloadTimestamp return timestamp
as
begin
return DownloadTimestamp;
end GetDownloadTimestamp;
end SyncVars;
/
3. Add a begin_download connection script to determine the timestamp value:
exec ml_add_lang_connection_script( 'version?', 'begin_download',
'SQL','begin SyncVars.SetDownloadTimestamp(); end;' );
4. Add a modify_next_last_download_timestamp connection script to
replace the timestamp that will be used by MobiLink:
exec ml_add_lang_connection_script(
'version?','modify_next_last_download_timestamp', 'SQL','begin ? :=
GetDownloadTimestamp(); end;' );
Then your table download_cursor scripts just need to use the usual
'WHERE last_modified >= ?' clause, since the value that MobiLink passes
in for the question mark will be the timestamp value determined in the
previous synchronization.
Other alternatives:
B. If the tables with long transactions are not involved in
synchronization, then you can reduce the duplication window of the above
by only considering open transactions that have pending changes on the
synchronized tables. Here's the corresponding SQL (assuming that the
synchronized tables are TABLE1 and TABLE2 with owner OWNERNAME):
select nvl( min( to_timestamp( start_time, 'mm/dd/rr hh24:mi:ss' ) ),
localtimestamp )
from v$transaction
where addr in
(select taddr
from v$session
where sid in
(select sid
from v$lock
where type = 'TM'
and id1 in
(select object_id
from dba_objects
where object_name in ('TABLE1', 'TABLE2' )
and owner = 'OWNERNAME')
)
)
The MobiLink connection will need permission for the base views
V_$SESSION, V_$LOCK as well as V_$TRANSACTION.
C. Update last_modified timestamps after commit instead of changing next
last download time.
- Have triggers set a modified flag instead of last_modified timestamp.
- Have a background process that periodically updates last_modified
timestamp and clears the modified flag for rows where modified flag is set.
- The download cursor should select by modified flag and last_modified
timestamp.
The duplication window is for this is the period between runs of the
background process. This would be preferred over A or B if long
transactions were expected to be longer than the period between runs of
the background process.
D. Use Oracle 10g's built-in ORA_ROWSCN row marker instead of
last_modified timestamp. ORA_ROWSCN is a pseudo column that holds the
commit system change number (SCN) for the row (if the table is created
with ROWDEPENDENCIES enabled) or the block. The commit SCN is unique for
each transaction.
- Create tables with rowdependencies (adds 6 bytes per row)
- Note and maintain last_download_scn (from
dbms_flashback.get_system_change_number())
- Download cursor should select rows where ORA_ROWSCN > last_download_scn
This technique has no duplication or missed rows, and no triggers are
required. However it requires Oracle 10g, tables created with
rowdependencies, and user maintenance of last_download_scn.
Cheers,
Graham