SQL Anywhere Mobile - Is lt=OFF necessary to upload RI violation diagnostics?

Author

Is lt=OFF necessary to upload RI violation diagnostics?

Breck Carter [TeamSybase]

2005-07-19, 9:24 am

Is it necessary to run dbmlsync 9.0.2.3124 on Windows XP SP2 with -e
lt=OFF when using an sp_hook_dbmlsync_dow
nload_log_ri_violati
on that
inserts to a table that in the publication because it is being
uploaded for centralized diagnostic purposes?

Here is what happens when dbmlsync is run with the default -e lt=ON
(or with -e lt=SHARE, which makes no difference); it gets stuck on the
call to the sp_hook:

I. 07/19 08:51:24. Synchronization Started
I. 07/19 08:51:24. 1: -c
I. 07/19 08:51:24. 2:
********************
********************
**************
I. 07/19 08:51:24. 3: -e
I. 07/19 08:51:24. 4: adr=host=localhost;s
v=PC v1
I. 07/19 08:51:24. 5: -vnosu
I. 07/19 08:51:24. 6: -x
I. 07/19 08:51:24. Adaptive Server Anywhere MobiLink Synchronization
Version 9.0.2.3124
....
I. 07/19 08:51:31. Resolving referential integrity violations on table
xxx, role FK_xxx
I. 07/19 08:51:31. Opening connection to remote database to call
error/log hook.
I. 07/19 08:51:31. insert into #hook_dict values( 'MobiLink user','2'
);
I. 07/19 08:51:31. insert into #hook_dict values( 'Foreign key
table','xxx' );
I. 07/19 08:51:31. insert into #hook_dict values( 'Primary key
table','yyy' );
I. 07/19 08:51:31. insert into #hook_dict values( 'Role name','FK_xxx'
);
I. 07/19 08:51:31. insert into #hook_dict values( 'script version','PC
v1' );
I. 07/19 08:51:31. insert into #hook_dict values( 'publication_0','PC'

);
I. 07/19 08:51:31. execute
"DBA". sp_hook_dbmlsync_dow
nload_log_ri_violati
on

The following commands were run to see what's going on:

BEGIN
CALL sa_conn_info();
SELECT * FROM sa_locks ( max_locks = 999999999 ) ORDER BY lock_name
DESC;
SELECT * FROM sa_locks ( max_locks = 999999999 ) WHERE table_name =
'DBA. dbmlsync_ri_violatio
n';
SELECT * FROM sa_locks ( max_locks = 999999999 ) WHERE lock_name IS
NULL;
END;

The first result set shows that (I think) the sp_hook connection is
blocked by the "main" dbmlsync connection:

Number,Name,Userid,D
BNumber,LastReqTime,
ProcessTime,Port,Req
Type,CommLink,NodeAd
dr,LastIdle,CurrTask
Sw,BlockedOn,LockNam
e,UncmtOps
9,'','DBA',0,'2005-07-19
08:53:17. 358',,,'CURSOR_OPEN'
,'local','',1923,,0,
0,0
8,'DBMLsync_error','
REMOTE_DBA',0,'2005-07-19
08:51:31. 246',,,'STMT_EXECUTE
_IMM','local','',0,,
5,1099511628312,7
7,'DBMLsync_scan','R
EMOTE_DBA',0,'2005-07-19
08:51:27. 918',,,'BACKUP_SET_T
RUNC','local','',77,
,0,0,0
5,'DBMLsync_main','R
EMOTE_DBA',0,'2005-07-19
08:51:31. 246',,,'STMT_DROP','
local','',0,,0,0,101
4

The second result set (1100+ rows not shown here) does not include
*any* 13-digit lock names like 1099511628312.

The third result set shows that the main dbmlsync connection has a
"table contents read lock":

connection,user_id,t
able_name,lock_type,
lock_name
5,'REMOTE_DBA','DBA. dbmlsync_ri_violatio
n','SAT',
8,'REMOTE_DBA','DBA. dbmlsync_ri_violatio
n','S',

The fourth result set shows that the main dbmlsync connection has SAT
or SPAT locks on *all* the tables being synchronized; is this
intended?

connection,user_id,t
able_name,lock_type,
lock_name
5,'REMOTE_DBA','DBA. dbmlsync_ri_violatio
n','SAT',
5,'REMOTE_DBA','DBA. dbmlsync_sql_error',
'SAT',
5,'REMOTE_DBA','DBA.xxx','SAT',
5,'REMOTE_DBA','DBA.yyy','SPAT',
....
5,'REMOTE_DBA','DBA.zzz','SPAT',

Although the sa_locks output is no help, the output from debugging
MESSAGE ... TO CONSOLE statements shows that the sp_hook procedure is
stuck on the INSERT shown below:

=====
CREATE PROCEDURE sp_hook_dbmlsync_dow
nload_log_ri_violati
on()
BEGIN

....etcetera

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 5' ) TO CONSOLE;

INSERT dbmlsync_ri_violatio
n (
...etcetera;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 6' ) TO CONSOLE;

END; -- sp_hook_dbmlsync_dow
nload_log_ri_violati
on
=====

The workaround is to run with -e lt=OFF, which seems rather extreme...
IMO dbmlsync should not block itself.

Is there another workaround that is not so extreme?

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Reg Domaratzki \(iAnywhere Solutions\)

2005-07-21, 9:24 am

Here's the response I got from our dbmlsync developer, since I was too
swamped to look at it...

When the locktables options is set to 'on' or 'shared' dbmlsync will lock
all the tables being synchronized at the beginning of synchronization and
hold those locks until the synchronization is complete. These locks are
created on the main dbmlsync database connection.

According to the documentation the
sp_hook_dbmlsync_dow
nload_log_ri_violati
on hook, "is called on a separate
connection from the one that dbmlsync uses for the download". I think that
from this it is clear that if locktables is on and you try to modify tables
being synchronized in the log_ri_violation hook you will create a deadlock.
There is nothing dbmlsync can do about this. We cannot release our own
locks because that would defeat the whole purpose of getting the locks in
the first place.

Here are 2 better ways to achieve what you're trying to do:

1) Use the sp_hook_dbmlsync_dow
nload_ri_violation hook to modify tables
being synchronized. This hook is called immediately before the
log_ri_violation hook and is called on the dbmlsync database connection so
it will not deadlock. The downside to this is that the hook is called while
the download is being applied and if dbmlsync later decides to rollback the
download any changes made in the hook will be rolled back and lost at the
same time.

2) Create a second table (not involved in synchronization) and log
violations to this table in the log_ri_violation hook. Since the table is
not involved in synchronization, it will not be locked and no deadlock will
result. You can then use the sp_hook_dbmlsync_beg
in hook to copy the rows
from the non-sync table into the table that is actually synchronized. The
sp_hook_dbmlsync_beg
in hook is called before the tables are locked and so
this should be successful.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:750qd152jtbiqp9
lnkt5oes9tji0fs7up0@
4ax.com...
> Is it necessary to run dbmlsync 9.0.2.3124 on Windows XP SP2 with -e
> lt=OFF when using an sp_hook_dbmlsync_dow
nload_log_ri_violati
on that
> inserts to a table that in the publication because it is being
> uploaded for centralized diagnostic purposes?
>
> Here is what happens when dbmlsync is run with the default -e lt=ON
> (or with -e lt=SHARE, which makes no difference); it gets stuck on the
> call to the sp_hook:
>
> I. 07/19 08:51:24. Synchronization Started
> I. 07/19 08:51:24. 1: -c
> I. 07/19 08:51:24. 2:
> ********************
********************
**************
> I. 07/19 08:51:24. 3: -e
> I. 07/19 08:51:24. 4: adr=host=localhost;s
v=PC v1
> I. 07/19 08:51:24. 5: -vnosu
> I. 07/19 08:51:24. 6: -x
> I. 07/19 08:51:24. Adaptive Server Anywhere MobiLink Synchronization
> Version 9.0.2.3124
> ...
> I. 07/19 08:51:31. Resolving referential integrity violations on table
> xxx, role FK_xxx
> I. 07/19 08:51:31. Opening connection to remote database to call
> error/log hook.
> I. 07/19 08:51:31. insert into #hook_dict values( 'MobiLink user','2'
> );
> I. 07/19 08:51:31. insert into #hook_dict values( 'Foreign key
> table','xxx' );
> I. 07/19 08:51:31. insert into #hook_dict values( 'Primary key
> table','yyy' );
> I. 07/19 08:51:31. insert into #hook_dict values( 'Role name','FK_xxx'
> );
> I. 07/19 08:51:31. insert into #hook_dict values( 'script version','PC
> v1' );
> I. 07/19 08:51:31. insert into #hook_dict values( 'publication_0','PC'

> );
> I. 07/19 08:51:31. execute
> "DBA". sp_hook_dbmlsync_dow
nload_log_ri_violati
on
>
> The following commands were run to see what's going on:
>
> BEGIN
> CALL sa_conn_info();
> SELECT * FROM sa_locks ( max_locks = 999999999 ) ORDER BY lock_name
> DESC;
> SELECT * FROM sa_locks ( max_locks = 999999999 ) WHERE table_name =
> 'DBA. dbmlsync_ri_violatio
n';
> SELECT * FROM sa_locks ( max_locks = 999999999 ) WHERE lock_name IS
> NULL;
> END;
>
> The first result set shows that (I think) the sp_hook connection is
> blocked by the "main" dbmlsync connection:
>
>
Number,Name,Userid,D
BNumber,LastReqTime,
ProcessTime,Port,Req
Type,CommLink,No
deAddr,LastIdle,Curr
TaskSw,BlockedOn,Loc
kName,UncmtOps[color
=darkred]
> 9,'','DBA',0,'2005-07-19
> 08:53:17. 358',,,'CURSOR_OPEN'
,'local','',1923,,0,
0,0
> 8,'DBMLsync_error','
REMOTE_DBA',0,'2005-07-19
> 08:51:31. 246',,,'STMT_EXECUTE
_IMM','local','',0,,
5,1099511628312,7
> 7,'DBMLsync_scan','R
EMOTE_DBA',0,'2005-07-19
> 08:51:27. 918',,,'BACKUP_SET_T
RUNC','local','',77,
,0,0,0
> 5,'DBMLsync_main','R
EMOTE_DBA',0,'2005-07-19
> 08:51:31. 246',,,'STMT_DROP','
local','',0,,0,0,101
4
>
> The second result set (1100+ rows not shown here) does not include
> *any* 13-digit lock names like 1099511628312.
>
> The third result set shows that the main dbmlsync connection has a
> "table contents read lock":
>
> connection,user_id,t
able_name,lock_type,
lock_name
> 5,'REMOTE_DBA','DBA. dbmlsync_ri_violatio
n','SAT',
> 8,'REMOTE_DBA','DBA. dbmlsync_ri_violatio
n','S',
>
> The fourth result set shows that the main dbmlsync connection has SAT
> or SPAT locks on *all* the tables being synchronized; is this
> intended?
>
> connection,user_id,t
able_name,lock_type,
lock_name
> 5,'REMOTE_DBA','DBA. dbmlsync_ri_violatio
n','SAT',
> 5,'REMOTE_DBA','DBA. dbmlsync_sql_error',
'SAT',
> 5,'REMOTE_DBA','DBA.xxx','SAT',
> 5,'REMOTE_DBA','DBA.yyy','SPAT',
> ...
> 5,'REMOTE_DBA','DBA.zzz','SPAT',
>
> Although the sa_locks output is no help, the output from debugging
> MESSAGE ... TO CONSOLE statements shows that the sp_hook procedure is
> stuck on the INSERT shown below:
>
> =====
> CREATE PROCEDURE sp_hook_dbmlsync_dow
nload_log_ri_violati
on()
> BEGIN
>
> ...etcetera
>
> MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 5' ) TO CONSOLE;
>
> INSERT dbmlsync_ri_violatio
n (
> ...etcetera;
>
> MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 6' ) TO CONSOLE;
>
> END; -- sp_hook_dbmlsync_dow
nload_log_ri_violati
on
> =====
>
> The workaround is to run with -e lt=OFF, which seems rather extreme...
> IMO dbmlsync should not block itself.
>
> Is there another workaround that is not so extreme?
>
> Breck
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:[/color]
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links