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 |
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 answered 30 Aug '19, 09:56 Christian Ha... 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 answered 31 Aug '19, 06:52 Breck Carter |
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...] answered 04 Sep '19, 06:48 Breck Carter |
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. answered 30 Aug '19, 10:14 Volker Barth 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
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
It should be possible, because sa_conn_info does show
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:
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
|