Hi, could someone please tell me how can I check the status of a table/row (if the the row is locked or not)?

Is there also a way to provoke locking a table (becuase only querying the row in dbisql does not always mean that the row is locked).

I have a scenario in which I insert a row and then I trigger an event in which this inserted row will be updated (over another connection). Everything works well but not always, because sometimes the event cant update the row, and I think that the reason is because the same row is being open/locked from another third connection.

Can I use @@error within the event to get the reason resulting of the attempt of updating the row?

Thanks in advance

asked 30 Aug '19, 07:58

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

edited 30 Aug '19, 08:00


You need to combine the sa_locks system procedure and the rowid function for this. There actually is an example on how to do this in the help: See http://dcx.sybase.com/index.html#sa160/en/dbreference/rowid-function.html

permanent link

answered 30 Aug '19, 09:56

Christian%20Hamers's gravatar image

Christian Ha...
697131633
accept rate: 42%

1

This is the correct answer. For a way more verbose version, see my new answer :)

(04 Sep '19, 06:50) Breck Carter

In addition to the other responses...

Foxhound does a good job of showing which connections are blocked and why, in real time.

Figure 1. The Foxhound Monitor Page Showing An Idle Server With One Blocked Connection

permanent link

answered 31 Aug '19, 06:52

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

First, some short answers...

> Hi, could someone please tell me how can I check the status of a 
> table/row (if the the row is locked or not)?

Call sa_locks()

> Is there also a way to provoke locking a table (becuase only querying 
> the row in dbisql does not always mean that the row is locked).

To lock a table: LOCK TABLE statement

To lock a row: UPDATE statement

> I have a scenario in which I insert a row and then I trigger an event 
> in which this inserted row will be updated (over another connection). 
> Everything works well but not always, because sometimes the event cant 
> update the row, and I think that the reason is because the same row is 
> being open/locked from another third connection.

IF you don't see the event in sa_conn_info() 
THEN it is not running. 

Maybe it finished normally, maybe it raised an exception and terminated,
maybe it was never started. If it raised an exception then an error message 
should appear in the server console diagnostic text file (dbsrv17.exe -o filepec.txt).

IF you see the event in sa_conn_info() but BlockedOn is zero 
THEN it is not blocked.

> Can I use @@error within the event to get the reason resulting of the 
> attempt of updating the row?

No.

Now, a longer answer...

There is no facility in SQL Anywhere for a blocked connection to "fail and try again", you have to build it.

By default, a blocked connection will either (1) wait forever (blocking ON, blocking_timeout 0), (2) wait for a while and then raise an exception (blocking ON, blocking_timeout not 0) or raise an exception right away (blocking OFF).

You can set BLOCKING temporarily, just for a leetle while, just for one statement if you want: SET TEMPORARY OPTION BLOCKING = 'OFF';

You can capture exceptions with an EXCEPTION handler.

Here's the template for the many many many exception handlers inside Foxhound:

--------------------------------------------------------------
-- Diagnose and handle an exception.

BEGIN
DECLARE @date      DATE;
DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

BEGIN
   SET @date = 'xxx';    -- raise an exception
EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, 
         ' SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) TO CONSOLE;
END;

MESSAGE STRING ( 'SQLCODE = ', @sqlcode ) TO CLIENT;
MESSAGE STRING ( 'SQLSTATE = ', @sqlstate ) TO CLIENT;
MESSAGE STRING ( 'ERRORMSG() = ', @errormsg ) TO CLIENT;

END;
--------------------------------------------------------------

Note that you can't MESSAGE TO CLIENT inside an event because ... there ... is ... no ... client :)

...you have to use MESSAGE TO CONSOLE.

Here's a demo (of your situation?) that you can run in two ISQL sessions against any SQL Anywhere database:

--------------------------------------------------------------
-- Connection 1: Set conditions for World Peace.
BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 1 );
COMMIT;

BEGIN
   DROP EVENT e;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE EVENT e
HANDLER BEGIN
   UPDATE t SET data = 2 WHERE pkey = 1;
END;

--------------------------------------------------------------
-- Connection 2: Start a war.

UPDATE t SET data = 3 WHERE pkey = 1;
-- no COMMIT

--------------------------------------------------------------
-- Connection 1: Try to do some peacekeeping.

TRIGGER EVENT e;
--------------------------------------------------------------

Here's what the sa_procedures show...

--------------------------------------------------------------
SELECT * FROM sa_conn_info() ORDER BY Name;

Number,Name,Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,
BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps,ParentConnection,LockObject,LockObjectType

1,'ddd17-1','dba',0,'2019-09-04 06:18:40.094','FETCH','local','',0,0,0,0,,'',0,,'',''
2,'ddd17-2','dba',0,'2019-09-04 06:16:32.656','PREFETCH','local','',0,0,0,0,,'',1,,'',''
1000000030,'e','dba',0,'','unknown (0)','NA','NA',0,0,2,39256064,,'dba.t',0,,'dba.t','TABLE'
3,'Foxhound-Monitor-18880','dba',0,'2019-09-04 06:18:39.532','COMMIT','local','',0,0,0,0,,'',0,,'',''
1000000001,'INT: StmtPerfMngrConn','',0,'','unknown (0)','NA','NA',0,0,0,0,,'',0,,'',''

SELECT * FROM sa_locks() ORDER BY conn_name;

conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,
   lock_type,row_identifier
'ddd17-2',2,'dba','BASE','dba','t',,'Row','Transaction','Intent',39256064
'ddd17-2',2,'dba','BASE','dba','t',,'Row','Transaction','WriteNoPK',39256064
'ddd17-2',2,'dba','BASE','dba','t',,'Table','Transaction','Intent',
'ddd17-2',2,'dba','BASE','dba','t',,'Schema','Transaction','Shared',
'e',1000000030,'dba','BASE','dba','t',,'Schema','Transaction','Shared',
--------------------------------------------------------------

Here's what Foxhound Version 5 (coming soon) shows:

[open in a new tab to see this full size...]

permanent link

answered 04 Sep '19, 06:48

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

and I think that the reason is because the same row is being open/locked from another third connection.

Well, unless you have set option "blocking" to "On" and "blocking_timeout" to the default 0 (which will wait until the lock is released), you should get a SQLE_LOCKED error within the event, so you do not need to guess the reason... - In other words, I would use an exception handler within the event's handler and check for that (and possibly other) errors.

permanent link

answered 30 Aug '19, 10:14

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

converted 02 Sep '19, 07:50

the "blocking_timeout" is 0 and it happens again sometimes that the event cannt update some row.

This is why I said 'I guess', because after leaving "blocking_timeout" to its default value of 0 I was expecting to avoid such locks.

(01 Sep '19, 14:37) Baron
Replies hidden

So the event "hangs" as it cannot lock the row(s) itself... Then you should be able to find the blocking connection via sa_conn_info, in addition to the other suggestions.

A timeout of 0 (aka "infinite") won't avoid locks, it simply keeps the blocked connection waiting instead of throwing an error.

(01 Sep '19, 15:57) Volker Barth

I have the following options:

Public.blocking = ON;

Public.blocking_timeout=20000;

MyEventUser.blocking_timeout=0;

I understand that setting the timeout to 0 won't avoid the lock, but I wonder why the event (in the name of MyEventUser) can never update the row (even at the end of day), because I am sure that at the end of the day all connections are disconnected from DB.

Should I understand that then if timeout is set to 0 then no error will be thrown? so the event will continously try to update the row but will never throw an error?

I think it is not possible to find the blocking connection via sa_conn_info, because this procedure returns only information about connected users, but how can I know which connected user is locking a specific row in a table?

(02 Sep '19, 03:19) Baron

so the event will continously try to update the row but will never throw an error?

Yes, the request will be blocked until the locks are released. (It's no active "waiting and trying to update".)

(02 Sep '19, 05:52) Volker Barth

I think it is not possible to find the blocking connection via sa_conn_info, because this procedure returns only information about connected users?

It should be possible, because sa_conn_info does show

  • all running connections (including those executing events, they have "high" connection IDs)

  • whether and by which other connection a connection is blocked (see column "BlockedOn")

  • and possibly on which rows it is blocked (see columns "LockRowID", "LockIndexID", "LockTable").

It's handy for a quick overview, methinks. As stated, the other suggestions should help to get more details.

(02 Sep '19, 05:59) Volker Barth
Comment Text Removed

Do you mean that it is active or it is NOT active? If it is not active, then it is so that the event will be informed as soon as the block is released on the wanted row, and only then the event is newly active and does updating the (not any more blocked) row?

(02 Sep '19, 09:55) Baron

Well, that's difficult to explain here (at least for me). You may have a look at the docs on topics like "locking conflicts" and the like.

When the database server processes a request that requires a lock it cannot currently acquire (say, your event runs as transaction A and wants to update a row that another transaction B has already tried to update, or the other transaction has established a read or intent lock), then the "blocking" option decides how to proceed:

  • If blocking is set to "Off", the transaction A will be rolled back throwing the mentioned error SQLE_LOCKED.

  • Otherwise, transaction A will be suspended unteil either the lock is resolved, and A can get it and proceed, or the blocking_timeout timespan has been reached, and the transaction will be rolled back as in the first case.

So, with my sentence "(It's no active "waiting and trying to update".)" I just wanted to express that the connection does not "try and try and try until it succeeds" but the database server will "pause" the connection's action until either the lock is freed or the maximum waiting time has been reached.

(02 Sep '19, 10:08) Volker Barth
showing 1 of 7 show all flat view
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:

×21

question asked: 30 Aug '19, 07:58

question was seen: 2,500 times

last updated: 04 Sep '19, 07:13