By default, dbmlsync locks the tables being synchronized before any upload scripts are called, and it maintains these locks until the download is committed. You can prevent table locking by setting the extended option LockTables to off.
When possible, it is recommended that you use the default table locking behavior. Doing scripted uploads without table locking significantly increases the number of issues you must consider and the difficulty of creating a correct and workable solution. This should only be attempted by advanced users with a good understanding of database concurrency and synchronization concepts.
When table locking is off, the isolation level at which your upload stored procedures run is very important because it determines how uncommitted transactions are handled. This is not an issue when table locking is on because table locks ensure that there are no uncommitted changes on the synchronized tables when the upload is built.
Your upload stored procedures run at the default isolation level for the database user who is specified on the dbmlsync command line unless you explicitly change the isolation level in your upload stored procedure.
Isolation level 0 is the default isolation level for the database, but it is recommended that you do not run your upload procedures at isolation level 0 when using scripted upload with no table locks. If you implement scripted upload without table locks and use isolation level 0, you may upload changes that are not committed, which could result in the following problems:
The uncommitted changes could be rolled back, which would result in incorrect data being sent to the consolidated database.
The uncommitted transaction may not be complete, in which case you might upload only part of a transaction and thus leave the consolidated database in an inconsistent state.
Your alternatives are to use isolation levels 1, 2, 3, or snapshot. All of these isolation levels ensure that you will not upload uncommitted transactions.
Using isolation levels 1, 2, or 3 could result in your upload stored procedures blocking if there are uncommitted changes on the table. Since your upload stored procedures are called while dbmlsync is connected to the MobiLink server, this could tie up server connections. If you use isolation level 1, you may be able to avoid blocking by using the READPAST table-hint clause in your select statements.
Snapshot isolation is a good choice since it prevents both blocking and reads of uncommitted changes.
If you choose to forgo table locking, you must have a mechanism for handling operations that are not committed when a synchronization occurs. To see why this is a problem, consider the following example.
Suppose a table is being synchronized by scripted upload. For simplicity, assume that only inserts are being uploaded. The table contains an insert_time column that is a timestamp that indicates the time when each row was inserted.
Each upload is built by selecting all the committed rows in the table whose insert_time is after the last successful upload and before the time when you started to build the current upload (which is the time when the sp_hook_dbmlsync_set_upload_end_progress hook was called). Suppose the following takes place.
|1:00:00||A successful synchronization occurs.|
|1:04:00||Row R is inserted into the table but not committed. The insert_time column for R is set to 1:04:00.|
|1:05:00||A synchronization occurs. Rows with insert times between 1:00:00 and 1:05:00 are uploaded. Row R is not uploaded because it is uncommitted. The synchronization progress is set to 1:05:00.|
|1:07:00||The row inserted at 1:04:00 is committed. The insert_time column for R continues to contain 1:04:00.|
|1:10:00||A synchronization occurs. Rows with insert times between 1:05:00 and 1:10:00 are uploaded. Row R is not uploaded because its insert_time is not in the range. In fact, row R will never be uploaded.|
In general, any operation that occurs before a synchronization but is committed after the synchronization is susceptible to loss in this way.
The simplest way to handle uncommitted transactions is to use the sp_hook_dbmlsync_set_upload_end_progress hook to set the end progress for each synchronization to the start time of the oldest uncommitted transaction at the time the hook is called. You can determine this time using the sa_transactions system procedure as follows:
SELECT min( start_time ) FROM sa_transactions()
In this case, your upload stored procedures must ignore the end progress that was calculated in the sp_hook_dbmlsync_set_upload_end_progress hook using sa_transactions and passed in using the #hook_dict table. The stored procedures should just upload all committed operations that occurred after the start progress. This will ensure that the download does not overwrite rows with changes that still need to be uploaded. It will also ensure that operations are uploaded in a timely manner even when there are uncommitted transactions.
This solution ensures that no operations will be lost, but some operations may be uploaded more than once. Your scripts on the server side must be written to handle operations being uploaded more than once. Below is an example that shows how a row can be uploaded more than once in this setup.
|1:00:00||A successful synchronization occurs.|
|2:00:00||Row R1 is inserted but not committed.|
|2:10:00||Row R2 is inserted and committed.|
|3:00:00||A synchronization occurs. Operations that occurred between 1:00 and 3:00 are uploaded. Row R2 is uploaded and the progress is set to 2:00 because that is the start time of the oldest uncommitted transaction.|
|4:00:00||Row R1 is committed.|
|5:00:00||A synchronization occurs. Operations that occurred between 2:00 and 5:00 are uploaded and the progress is set to 5:00. The upload contains rows R1 and R2 because they both have timestamps within the upload range. Thus R2 has been uploaded twice.|
If your consolidated database is SQL Anywhere, you can handle redundantly uploaded insert operations by using the INSERT ... ON EXISTING UPDATE statement in your upload_insert script in the consolidated database.
For other consolidated databases, you can implement similar logic in a stored procedure that is called by your upload_insert script. Just write a check to see if a row with the primary key of the row being inserted already exists in the consolidated database. If the row exists update it, otherwise insert the new row.
Redundantly uploaded delete and update operations are a problem when you have conflict detection or resolution logic on the server side. If you write conflict detection and resolution scripts on the server side, they must be able to handle redundant uploads.
Redundantly uploaded deletes can be a major concern if primary key values can be reused by the consolidated database. Consider the following sequence of events:
Row R with primary key 100 is inserted into a remote database and uploaded to the consolidated database.
Row R is deleted on the remote database and the delete operation is uploaded.
A new row R' with primary key 100 is inserted into the consolidated database.
The delete operation on row R from step 2 is uploaded again from the remote database. This could easily result in R' being deleted inappropriately from the consolidated database.