I'm trying to change the default action code from 3000 to 1000, and I used the example from the documentation like below, but it doesn't work. Once MLSRV17 encounters an error then it tries it 10 times and then stop with (synchronization failed).

call ml_add_connection_script(

'ver1',

'handle_error',

'call MyLog({ml s.action_code})'

);

create or replace procedure MyLog(inout @action_code int)

begin

set @action_code = 1000;

end;

asked 21 Jul, 04:28

Baron's gravatar image

Baron
1.4k95108130
accept rate: 50%

edited 21 Jul, 04:39

Oh, I got it, the handle_error doesn't consider errors related to locked tables (which was in my case).

If the error is related for example to primary key violation, then it will be handled.

The question is, how can I catch errors related to locked data?

(21 Jul, 05:03) Baron
Replies hidden

Do you need to catch those errors, or can you prevent them?

(21 Jul, 08:16) Volker Barth

first catch and log then try to avoid

(21 Jul, 08:20) Baron

> errors related to locked tables

What exactly do you mean by that? What were the symptoms?

(21 Jul, 09:24) Breck Carter

What DBMS are you using for the consolidated database?

SQL Anywhere? Oracle? SQL Server? etcetera

(21 Jul, 09:25) Breck Carter

The consolidated is a SQL Anywhere 17.

With that symptom I mean the following: A client has updated a row in Table1 and tries to synchronise this update to cons., at this moment the cons. updates also the same row in Table1 locally. As a result the dbmlsync on the client fails.

Since many applications are running against my cons. and since each of them can update the row in Table1 I need to catch the error and try to tell which application is blocking the row!

(21 Jul, 09:35) Baron

I tried the following:

call ml_add_table_script('ver1', 'Table1', 'upload_update', 'call UpdateTable1({ml r.col1}, {ml r.col2})');

create or replace procedure UpdateTable1(@col1 varchar(10), @col2 varchar(10))

begin

update Table1 set col1 = @col1 where col2 = @col2;

exception

when others then

insert into MYLOG select * from sa_locks();

end;

The Problem is here that I need to repeat the same job for each table

(21 Jul, 09:41) Baron

My MobiLink skills are a bit rusty, but here goes...

If your consolidated DBMS is SQL Anywhere, I believe mlsrv17.exe does NOT change either of these two option defaults: blocking = on (which means wait for block to go away by itself), and blocking_timeout = 0 (which means never timeout)

You can use SET TEMPORARY OPTION to change both or either of these in the MobiLink begin_connection script...

blocking = off to raise an exception as soon as the connection is blocked

blocking = on with blocking_timeout = 10000 to raise an exception if the connection is blocked for 10 seconds

Here's a template for writing all exception diagnostics to the dbsrv17 -o text file, which can be used in MobiLink scripts that raise exceptions...

BEGIN
DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );
-- code that may raise an exception
EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, 
         ' ERROR: SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg ) TO CONSOLE; END;

(21 Jul, 09:43) Breck Carter

Could you please post the portion of the MobiLink Log from the time you first see the locking warnings to the time the synchronization fails? This should give us a better idea how your consolidated database is setup WRT locking/blocking and we'll be able to see the exact symptoms you are seeing.

Without this, we are simply guessing at how your environment is setup, so it's difficult to make suggestions.

Thanks, Reg

(21 Jul, 10:05) Reg Domaratzki

I think MLSRV17 has its own options since these differ from what I see in SYSOPTION:

In the logfile of MSLRV17 I see the following:

Maximum number of deadlock retries: 10

Maximum delay between retries after deadlock: 30 seconds

Yes I used also the same approach, but as said, I need to do the same for each table, since there is no any connection script event which can catch locks (ODBC State = 40001, Native error code = -210)

(21 Jul, 10:08) Baron

Is your problem related to deadlocks, or just locking/blocking?

(21 Jul, 10:10) Reg Domaratzki

W. 2021-07-20 16:25:36. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:25:36. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:26:00. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:26:20. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:26:20. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:26:39. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:26:59. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:26:59. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:27:23. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:27:43. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:27:44. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:27:46. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:28:06. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:28:06. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:28:22. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:28:42. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:28:42. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:29:10. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:29:30. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:29:30. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:29:47. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:30:07. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:30:07. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:30:30. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:30:50. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:30:50. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:31:12. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:31:32. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:31:32. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:31:42. <1> [10045] Retrying the upload after deadlock in the consolidated database

W. 2021-07-20 16:32:02. <1> [10050] ODBC: [SAP][ODBC Driver][SQL Anywhere]User 'User1' has the row in 'TABLE1' locked (ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:32:02. <1> ROLLBACK Transaction: Upload

E. 2021-07-20 16:32:02. <1> [-10021] Unable to retry the current transaction after deadlock in the consolidated database. The retry limit has been reached

(21 Jul, 10:19) Baron

Do you think that I can prevent it? If another connection is blocking a row, then has the MLSRV17 any chance to release the block (rather than dropp connection)?

(21 Jul, 11:48) Baron
More comments hidden
showing 5 of 13 show all flat view

Locking is a special case in which the handle_error event is not called because the ML Server will (when possible) detect that locking has occured and retry, based on the same values you specified for deadlocks when using the -r and -rd switches on the ML Server start line.

Your solution for an SP with an exception handler is one way to handle this from the database, but it will execute a number of times, since the ML Server will retry. Another alternative to capture information using the MobiLink Server would be to implement a Java log listener looking for the -10021 error that the MobiLink Server posts to the log after all attempts to retry have been exhausted. Within the Java code for the log listener, make another connection to the database, and then execute and capture the output from the sa_locks() call. The java code for your very simple log listener might look something like :

import com.sap.ml.script.*;
import java.sql.*;
import java.util.*;

public class Example {

    Connection _log_connection;

    class MyLogListener implements LogListener {

        public void messageLogged( ServerContext sc, LogMessage msg )
        {
            String user;

            if( msg.getText().indexOf("[-10021]") != -1 ) {
                try { 
                    PreparedStatement ps_log = _log_connection.prepareStatement( "insert into track_locks select ?,CURRENT TIMESTAMP,* from sa_locks()" );
                    user = msg.getUser();
                    if( user == null ) {
                        user = "UserIsNULL";
                    }
                    ps_log.setString( 1, user );
                    ps_log.executeUpdate();
                    ps_log.close();
                    _log_connection.commit();
                } catch( Exception e ) {
                    // print some error output to the Mobilink log
                    e.printStackTrace();
                }
            }
        }
    }

    public Example( ServerContext sc ) throws SQLException {
        _log_connection = sc.makeConnection();
        sc.addErrorListener( new MyLogListener() );
    }

}

Next, you just need to add -sl java to the ML Server start line.

-sl java(-DMLStartClasses=Example -cp c:\path\to\dir\where\Example\dot\class\exists)" 

Reg

permanent link

answered 21 Jul, 11:29

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.1k340111
accept rate: 38%

edited 21 Jul, 11:53

2

Reg: Why does MobiLink say "deadlock" when SQLCODE -210 is a simple block?

W. 2021-07-20 16:28:06. <1> [10050] ODBC: [SAP][ODBC Driver]
[SQL Anywhere]User 'User1' has the row in 'TABLE1' locked 
(ODBC State = 40001, Native error code = -210)

I. 2021-07-20 16:28:06. <1> ROLLBACK Transaction: Upload

W. 2021-07-20 16:28:22. <1> [10045] Retrying the upload after 
   deadlock in the consolidated database

The Help says this: "Deadlock - When an upload is being applied to the consolidated database, it may encounter deadlock due to concurrency with other transactions. These transactions might be upload transactions from other MobiLink server database connections, or transactions from other applications using the consolidated database. When an upload transaction is deadlocked, it is rolled back and the MobiLink server automatically starts applying the upload again, from the beginning."

Sadly, that is a description of a block, not a deadlock. SQL Anywhere resolves true deadlocks instantly by picking one of the two deadlocked connections and giving one of them an exception.

(21 Jul, 15:13) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×365

question asked: 21 Jul, 04:28

question was seen: 143 times

last updated: 21 Jul, 15:16