Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » MobiLink - Server Administration » Synchronization techniques

MobiLink isolation levels

MobiLink connects to a consolidated database at the most optimal isolation level it can, given the isolation levels enabled on the RDBMS. The default isolation levels are chosen to provide the best performance while ensuring data consistency.

In general, MobiLink uses the isolation level SQL_TXN_READ_COMMITTED for uploads, and if possible, it uses snapshot isolation for downloads. If snapshot isolation is not available MobiLink uses SQL_TXN_READ_COMMITTED for downloads. A download using SQL_TXN_READ_COMMITTED isolation has the potential to block until another transaction completes. Such blocking can significantly decrease the throughput of synchronizations. When a download transaction performs no updates, which is recommended, snapshot isolation eliminates the problem of downloads being directly blocked by other transactions.

Snapshot isolation can result in duplicate data being downloaded (if, for example, a long-running transaction causes the same snapshot to be used for a long time), but MobiLink clients automatically handle this, so the only penalty is transmission time and the processing effort at the remote. Nevertheless, avoiding long-running transactions is recommended.

Isolation level 0 (READ UNCOMMITTED) is generally unsuitable for synchronization and can lead to inconsistent data.

The isolation level is set immediately after a connection to the consolidated database occurs. Some other connection setup also occurs at that time, and then the transaction is committed. The COMMIT is required by most RDBMSs so that the isolation level (and perhaps other settings) can take effect.

SQL Anywhere version 10 and later consolidated databases

SQL Anywhere versions 10 and later support snapshot isolation. By default, MobiLink uses the SQL_TXN_READ_COMMITTED isolation level for uploads, and snapshot isolation for downloads.

MobiLink can only use snapshot isolation if you enable it in your SQL Anywhere consolidated database. If snapshot isolation is not enabled, MobiLink uses the default SQL_TXN_READ_COMMITTED.

Enabling snapshot isolation for SQL Anywhere is recommended to avoid the improbable scenario of missing rows on download, especially if you expect to have uncommitted changes during synchronization that are later rolled back. For example, at SQL_TXN_READ_COMMITTED isolation level, SQL Anywhere does not block queries on rows where an uncommitted change to the row would change it from being selected to not being selected. If such a change is uncommitted during a synchronization with a remote database, thereby causing the row to be missed by the download cursor, and the change is subsequently rolled back, the row may never be downloaded to that remote database.

Enabling a database to use snapshot isolation can affect performance because copies of all modified rows must be maintained, regardless of the number of transactions that use snapshot isolation.

You can enable snapshot isolation for upload with the mlsrv17 -esu option, and disable snapshot isolation with the mlsrv17 -dsd option. If you need to change the MobiLink default isolation level in a connection script, you should do so in the begin_upload or begin_download scripts. If you change the default isolation level in the begin_connection script, your setting may be overridden at the start of the upload and download transactions.

SQL Anywhere versions earlier than version 10 consolidated databases

If you are using a version of SQL Anywhere earlier than version 10, the default MobiLink isolation level is SQL_TXN_READ_COMMITTED. You can change the default for the entire MobiLink session in the begin_connection script, or change it for the upload and download in the begin_upload and begin_download scripts, respectively.

Adaptive Server Enterprise consolidated databases

For Adaptive Server Enterprise, the default MobiLink isolation level is SQL_TXN_READ_COMMITTED. You can change the default for the entire MobiLink session in the begin_connection script, or change it for the upload and download in the begin_upload and begin_download scripts, respectively.

By default, the MobiLink server assumes datarows locking, which is non-blocking, so it uses the oldest open transaction start time for the next last download timestamp to not miss rows on download. If datarows locking is not used, then you can use the -dr mlsrv17 option.

Oracle consolidated databases

Oracle supports snapshot isolation, but calls it READ COMMITTED. By default, MobiLink uses the snapshot/READ COMMITTED isolation level for upload and download.

You can change the default for the entire MobiLink session in the begin_connection script, or change it for the upload and download in the begin_upload and begin_download scripts, respectively.

For the MobiLink server to make the most effective use of snapshot isolation, the Oracle account used by the MobiLink server must have permission for the GV_$TRANSACTION Oracle system view. If it does not, a warning is issued and rows may be missed on download. Only SYS can grant this access. The Oracle syntax for granting this access is:

grant select on SYS.GV_$TRANSACTION to user-name;
Microsoft SQL Server 2005 and later consolidated databases

Microsoft SQL Server 2005 supports snapshot isolation. By default, MobiLink uses the SQL_TXN_READ_COMMITTED isolation level for uploads, and snapshot isolation for download.

MobiLink can only use snapshot isolation if you enable it in your Microsoft SQL Server consolidated database. If snapshot is not enabled, MobiLink uses the default SQL_TXN_READ_COMMITTED. See your Microsoft SQL Server documentation for details.

You can enable snapshot isolation for upload with the mlsrv17 -esu option, and disable snapshot isolation with the mlsrv17 -dsd option. If you need to change the MobiLink default isolation level in a connection script, you should do so in the begin_upload or begin_download scripts. If you change the default isolation level in the begin_connection script, your setting may be overridden at the start of the upload and download transactions.

To use snapshot isolation on Microsoft SQL Server, the user ID that you use to connect the MobiLink server to the database must have permission to access the Microsoft SQL Server system table SYS.DM_TRAN_ACTIVE_TRANSACTIONS. If this permission is not granted, MobiLink uses the default level SQL_TXN_READ_COMMITTED.

If your consolidated database is running on a Microsoft SQL Server that is also running other databases, and if you are using snapshot isolation for uploads or downloads, and if your upload or download scripts do not access any other databases on the server, you should specify the MobiLink server -dt option. This option makes MobiLink ignore all transactions except ones within the current database, and potentially increases throughput and reduces duplication of rows that are downloaded.