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 that is not possible, it uses SQL_TXN_READ_COMMITTED). Snapshot isolation eliminates the problem of downloads being blocked until transactions are closed on the consolidated database.
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.
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 database occurs. Some other connection setup occurs, 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 supports 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 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. See Enabling snapshot isolation.
You can enable snapshot isolation for upload with the mlsrv11 -esu option, and disable snapshot isolation with the mlsrv11 -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.
See -esu option and -dsd option.
If you are using a version of SQL Anywhere prior to 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.
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.
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 be able to make the most effective use of snapshot isolation, the Oracle account used by the MobiLink server must have permission for the V_$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.V_$TRANSACTION to user-name |
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 SQL Server consolidated database. If snapshot is not enabled, MobiLink uses the default SQL_TXN_READ_COMMITTED. See your SQL Server documentation for details.
You can enable snapshot isolation for upload with the mlsrv11 -esu option, and disable snapshot isolation with the mlsrv11 -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.
See -esu option and -dsd option.
To use snapshot isolation on SQL Server, the user ID that you use to connect the MobiLink server to the database must have permission to access the 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, 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. It increases throughput and reduces duplication of rows that are downloaded.
See -dt option.
If you are using a version of Microsoft SQL Server prior to version 2005, 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.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |