SQL Anywhere Mobile - Is access v_$transaction required or optional?

Author

Is access v_$transaction required or optional?

Breck Carter

2005-06-07, 1:24 pm

When using dbmlsrv9.exe 9.0.2.3021 or later with Oracle, is
it absolutely necessary to have access to v_$transaction?

Or is the warning message benign, indicating that MobiLink
is going to behave as it used to before the enhancement
described below?

Breck

================(Bui
ld #3021 - Engineering Case
#377900)============
====

When using timestamp-based downloads with MobiLink and an
Oracle consolidated
database, special steps are required 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, assuming that
triggers are used
to record "last modified" timestamps for each row. In other
RDBMSes, the
download will block until changes are committed, assuming
MobiLink's default
isolation level, so changes are not missed.

To help download scripts avoid this problem, the MobiLink
server now sets
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 the by the
MobiLink server must have permission for the V_$TRANSACTION
Oracle system
view, otherwise the following warning message will be
displayed:

Unable to access ORACLE table 'v_$transaction'. Access is
needed in order
to ensure no data is missed if using timestamp-based
downloads.

Note that 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). The syntax
is:

grant select on SYS.V_$TRANSACTION to <user_name>

Workaround:
For earlier versions of the MobiLink server, equivalent
functionality can
be achieved with the following procedure:

The simplest way to make sure that all changes are
downloaded, assuming
a trigger is used to maintain a last_modifed 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_dow
load_timestamp
connection event for
modifying the next "last download time", but it is invoked
at the end of
the download transaction, and the new value needs to be
determined at the
beginning of the download transaction. So the value needs to
be determined
in the begin_download connection event, and used in the
modify_next_last_dow
nload_timestamp
event. In this example 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 GRANT access is not wanted on the whole view, instead
define a view that
only allows access to the required start_time column, as in
the following
example:

create view transaction_start_ti
mes
as
select start_time from v$transaction;

grant select on transaction_start_ti
mes 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_connecti
on_script( 'version?',
'begin_download', 'SQL','begin
SyncVars. SetDownloadTimestamp
(); end;' );

4. Add a modify_next_last_dow
nload_timestamp connection
script to replace
the timestamp that will be used by MobiLink:

exec ml_add_lang_connecti
on_script(
'version? ','modify_next_last_
download_timestamp',

'SQL','begin ? := GetDownloadTimestamp
(); end;' );

Then the 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.

Graham Hurst

2005-06-08, 11:24 am

I guess it depends on what you mean by "benign". Yes you do get the old
behaviour without access to v_$transaction. However with the old
behaviour you can potentially miss downloading rows.

Cheers,

Graham

Breck Carter wrote:

> When using dbmlsrv9.exe 9.0.2.3021 or later with Oracle, is
> it absolutely necessary to have access to v_$transaction?
>
> Or is the warning message benign, indicating that MobiLink
> is going to behave as it used to before the enhancement
> described below?
>
> Breck
>
> ================(Bui
ld #3021 - Engineering Case
> #377900)============
====
[snip]

Sponsored Links