Issue with SELECT ... FOR UPDATE db2 LUW

Showing results for 
Search instead for 
Do you mean 
Reply
Highlighted
Participant I
Posts: 1
Registered: ‎07-04-2018
#1 of 2 1,288

Issue with SELECT ... FOR UPDATE db2 LUW

Hi!

 

We are a issue in our database DB2 11.1 with a SELECT ... FOR UPDATE which is executed many time by seconds and it is generating many locks and the applications hangs.

 

SQL_TEXT:
SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE


The ATMDEVICESTATE table only has 1063 rows and two indexes:

INDEX ATMDEVICESTATE_1 ("LASTMSG_TIME" ASC,"GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES INCLUDE NULL KEYS ALLOW REVERSE SCANS
UNIQUE INDEX ATMDEVICESTATE_IX ("INSTITUTIONID" ASC, "GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES INCLUDE NULL KEYS ALLOW REVERSE SCANS


Is there a way to prevent these locks generated by the application in the database by SELECT ... FOR UPDATE with some parameter in the instace/database or howto can avoid this locks ???

 

In the top 5 SQL by executions appears the sentences SELECT...FOR UPDATE, the UPDATE of CURSOR and SELECT, the tree for the same table ATMDEVICESTATE:

EXECUTIONS TIME_SECONDS TEXT
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4617270 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE
4616826 0 UPDATE atmdevicestate SET flag1 = ?,flag2 = ?,flag3 = ?,status1 = ?,status2 = ?,status3 = ?,status4 = ?,status5 = ?,status_ext = ?,device_fitness = ?,cassette_fitness = ?,configid = ?,last_cmdtype = ?,last_txntype = ?,oar_screen = ?,lastmsg_time = ?,trace = ?,tpdu = ?,emv_identifier = ?,language = ?,pcode = ?,respcode = ?,acct_num = ?,acct_num2 = ?,tvn = ?,luno = ?,cap_date = ?,msg_coord_num = ?,oar_line = ?,model_specific = ?,node = ?, cassette_dispense = ?, statetablename = ?, statetablever = ?, fault = ?, severity = ?, availdate = ?, availtime = ?,cmdmbid = ?, balancerid = ?, site_id = ?, shclog_id = ?, last_modify_dttime = ? WHERE CURRENT OF SQL_CURSH200C4
3174610 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ?


We are think it's a programming issue, but the applications say than is a problem with the db2sync since his analysis they are observed high time in commits/checkpoints in the database

IBM team said there is no way to avoid this locks. We are check isolation level but we do not know what level of isolation is recommended to reduce/avoid blocking in the application side ???


-> The history file is less than 5 mb
-> Lock waits are not so high in the snapshots
-> Query execution times are good


Thks !

Centrify Guru I
Posts: 2,430
Registered: ‎07-26-2012
#2 of 2 1,199

Re: Issue with SELECT ... FOR UPDATE db2 LUW

@villalva,

 

Welcome to the Centrify forums.

This seems to be a DB2 database question.  Are you using our DB2 SSO Plugin?

 

We'd love to help, but this seems outside of our scope of knowledge.

 

Please clarify and let's go from there.  Otherwise, remember that as a Centrify customer you have SLA-based support.

 

R.P

Want to learn more about practical Centrify examples? Check out my blog at http://centrifying.blogspot.com
Follow Centrify: