|
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).
|
|
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 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 '21, 15:13)
Breck Carter
|
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?
Do you need to catch those errors, or can you prevent them?
first catch and log then try to avoid
> errors related to locked tables
What exactly do you mean by that? What were the symptoms?
What DBMS are you using for the consolidated database?
SQL Anywhere? Oracle? SQL Server? etcetera
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!
I tried the following:
The Problem is here that I need to repeat the same job for each table
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;
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
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)
Is your problem related to deadlocks, or just locking/blocking?
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
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)?