锁与并发.doc

锁与并发1、 locktimeout 为-1 ：应用程序将会出现无穷锁等待现象。生产系统中的OLAP 设置为60秒对于OLTP大约为10秒比较好。锁等待会造成以下几种结果1、 引起死锁，邮死锁检测器处理2、 等待超时，返回SQLCODE -911 和原因代码68 并自动回滚。3、 locktimeout 为-1 ：应用程序将会出现无穷锁等待现象4、 对于表、行、数据分区和MDC块锁定，应用程序根据业务逻辑需要使用SET CURRENTLOCK TIMEOUT 来覆盖数据库级别的locktimeout设置锁等待问题解决流程和步骤1、 把数据库监控开关打开db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on db2 update monitor switches using lock on sort on bufferpool on uow on table on statement on2、 利用DB2表函数编写一个监控SQL脚本 Select agent_id,substr(stmt_test,1,100) as statement, STMT_ELAPSED_TIME_MS from table(SNAPSHOT_STATEMENT(SAMPLE,-1) AS B where agent_id in(select agent_id_holding_lk from table(SNAPSHOT_LOCKWAIT(SAMPLE,-1) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY) order by STMT_ELAPSED_TIME_MS DESCSAMPLE 数据库名称此语句按照执行时间的长短的先后顺序排列出所有造成lockwait的语句。3、 写个脚本SHELL 每三秒钟运行一次，把监控结果管道累加输出到一个文件 #!/usr/bin/ksh#Dbname=$1Filename=find.locksql.$(date+%m%d%H%M%S)touch $filenameEcho now ,connect to database: $dbnameDb2 “connect to $dbname”Db2 “update dbm cfg using DFT_MON_LOCK ON DFT_MON_STMT ON”DB2 “Db2 update monitor switches using lock on sort on bufferpool on uow on table on statement on”Echo now ,finding the sqls which mode lockwaitDb2 “Select agent_id,substr(stmt_test,1,100) as statement, STMT_ELAPSED_TIME_MS from table(SNAPSHOT_STATEMENT(SAMPLE,-1) AS B where agent_id in(select agent_id_holding_lk from table(SNAPSHOT_LOCKWAIT(SAMPLE,-1) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY) order by STMT_ELAPSED_TIME_MS DESC” filenameEcho the sqls have save to the file $filename4、 一旦定位引起锁等待的SQL语句后，如果该SQL语句写的效率很低，可以对SQL作相应的调整，尝试建最合理的索引和调优SQL语句5、 如果4不能解决问题，可以根据业务逻辑选择UR隔离级别（只能解决读的问题）6、 在业务逻辑允许的情况下，考虑设置DB2_EVALUNCOMMITTED、DB2_SKIPDELETED、DB2_SKIPINSERTED来提高并发7、 最后考虑能否能引起锁等待的SQL语句关联的表做数据归档、业务分享等。锁定等待策略通过SET CURRENT LOCK TIMEOUT可以在语句级别指定。捕获引起锁等待的SQLDB2 GET SNAPSHOT FOR DATABASE ON PROD |GREP i LOCKLOCK WAITS 表示有大量锁等待Locks held currently = 45Lock waits = 18表示有大量锁Time database waited on locks (ms) = 671008 Lock list memory in use (Bytes) = 66560Deadlocks detected = 0 死锁检测Lock escalations = 0 死锁升级Exclusive lock escalations = 0 扩展大小Agents currently waiting on locks = 1Lock Timeouts = 0Internal rollbacks due to deadlock = 0Number of MDC table blocks pending cleanup = 0 Memory Pool Type = Lock Manager Heap确定哪个事务包含锁定db2pd db jltbm locks showlocks wait 在Sts(状态)列中查找带”W” （表示等待）的行。“所有者”列指示哪个事务持有该锁定。-G 表示被授权获得锁 -W：表示WAIT等待运行db2pd d dbname transactions 来标识正在运行事务的应用程序，该事务正在持有锁定。运行db2pd agents来标识正在运行的应用程序代理进程运行db2 get snapshot for application agentid APPHANDLID(对应APPHANDL值) ：-得到引起锁等待的agentid运行db2 get snapshot for application agentid ID-应用程序句柄，定位引起锁等待的SQL语句db2pd -hUsage: -q | -quit | q |quit Quit -h | -help file= Help -v | -version file= Version -osinfo disk file= Operating System Information -dbpartitionnum , Database Partition Number(s) -alldbpartitionnums All partition numbers -database | -db , Database(s) -alldatabases | -alldbs All Active Databases -inst Instance scope output -file All Output to Filename -command Read in predefined options -interactive Interactive -full Expand output to full length -repeat num sec count Repeat every num seconds (default 5) count times -everything All options on all database partitionsInstance scope options: -agents db= | app= file= Agents -fcm hwm file= FCM Information -memsets file= Memory Sets -mempools file= Memory Pools -memblocks dbms, fcm, fmp, appctl , private | all top blocks sort PoolID Memory Blocks -dbmcfg file= DBM Config -sysplex db= file= Sysplex List -utilities file= Utilities -fmp FMP Table -latches group file= Latches -ha HA Stats -gfw Monitor Fast Writer Information -edus List all EDUs in the instance -addnode oldviewapps detail Addnode information -dbptnmem Lists Database Partition Memory statistics -stack all Produce stack trace for all TIDs in db2sys.exe.Database scope options: -applications | agent= file= Applications -transactions | app= file= Transactions -bufferpools file= Buffer Pools -pages summary file= Bufferpool Pages -logs file= Transaction Logs -locks file= showlocks wait Locks -tablespaces group file= Tablespaces/Containers -dynamic anch= file= Dynamic Cache -static file= Static Cache -memsets file= Memory Sets -mempools file= Memory Pools -memblocks dbms, fcm, fmp, appctl , private | all top blocks sort PoolID Memory Blocks -dbcfg file= Database Config -catalogcache file= Catalog Cache -tcbstats nocatalog all|index file= Table Control Block Stats -reorgs file= Table Reorg Stats -recovery file= Recovery Status -reopt file= Reoptimized SQL Statements -storagepaths Storage Paths -hadr file= High Availability Disaster Recovery -activestatements apphdl= file= Active Statement List -workloads group Workloads -serviceclasses Service Classes -workactionsets group Work Action Sets -workclasssets group Work Class Sets -thresholds Thresholds -statisticscache summary | detail | find schema= object= Statistics Cache -apinfo all| all| file= List detailed information about the applicati