I have the need to update a table after a transaction is finished. To achieve this I use a EVENT that is started in a TRIGGER. To allow the transaction to finish I us WAITFOR DELAY inside the EVENT HANDLER. Some times it happened that the transaction in the EVENT caused a (dead) lock to the normal operations.
To reduce the risk of a block I would like that the EVENT tasks don't wait forever to get a lock.
CREATE EVENT "DONT_BLOCK_EVENT" ENABLE AT ALL
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
-- If we can't get a Lock wait maximum 1000ms
SET TEMPORARY OPTION blocking_timeout = 1000;
-- If this event blocks other connections we give up after 500ms
SET TEMPORARY OPTION blocking_others_timeout = 500;
if CAST( EVENT_PARAMETER('NumActive') as INTEGER ) = 1 then
-- Hold the processing to give the calling worker the possibility to finish its task
WAITFOR DELAY '00:00:05';
// Do some Stuff here
WHEN OTHERS THEN
select string ( 'EXCEPTION: SQLCODE = ', SQLCODE,
', SQLSTATE = ', SQLSTATE,
', ERRORMSG() = ', ERRORMSG() ) into @errormsg;
message @errormsg type info to console;
When I run
trigger event DONT_BLOCK_EVENT from iSQL the console I get
EXCEPTION: SQLCODE = -201, SQLSTATE = 42W17, ERRORMSG() = Invalid setting for option 'blocking_others_timeout'
Probably I have missunderstood the concept here. Would be great if somebody can enlighten me.
18 Sep '14, 03:52