How to check the database situation from error message and find the solution when LOCK TIME-OUT occurs?
1) Check error message:
Lock time-out is normal behavior for Database, the cause of Lock time-out is waiting time beyond the maximum time setting when competition exists in multiple transactions. (Timeout interval default value is 5 seconds, you cans use “set lock timeout n seconds” reset the interval, also can set DB_LTimO keyword in dmconfig.ini and then restart database to apply)
We can check the Lock situation via following ways.
One way is you can set keywords in dmconfig.ini
DB_LGDIR=<you log path>
DB_LGLCK=1
DB_LGSVR=3 ;( 1......6)
If you want to more information you can set DB_LGSVR>3 and you can check the error message about lock time-out in directory which specified by DB_LGDIR When lock time-out occur.
View the error message:
File “db_name_data.LOG”:
LOG_TIME,BEG_TIME,STATE,RETCODE,EXE_TIME,SV_FUNC,CONNECT_ID,USERNAME,LOGIN_TIME,LOGIN_ADDR,STMT_ID,ERROR_ARG,OTHER_INFO,SQL_CMD
"2010/06/12 15:01:29","2010/06/12 15:01:24","X", 1102,5.01,"EXECUTE ", 3868,"SYSADM","2010/06/12 14:59:56","local",0,": Object ID : 2.18.12 ,Connection ID : 1976, LKMode : X .NONE, LKStatus : GRANTED, Command : [EXIT] insert into t1 values(16,'Skirt') [unknown 1096], 501507, 0, 0","INFO_03868_1","update t1 set c2 = 'Hello' where c1 = 16"
"2010/06/12 15:07:25","2010/06/12 15:07:25","X", 6151,0.00,"PREPARE ", 1976,"SYSADM","2010/06/12 14:59:52","local",0,": 123 [bisort.c 294], 122, 2, 0",,"select * from t1 order by 123"
"2010/06/12 15:07:34","2010/06/12 15:07:34","X", 6151,0.00,"PREPARE ", 1976,"SYSADM","2010/06/12 14:59:52","local",0,": 3 [bisort.c 294], 2, 2, 0",,"select * from t1 order by 1,2,3"
File “db_name_data.TXT”:
INFO_03868_1 --------------------
[ERR_LK_TIMEOUT]:: Object ID : 2.18.12 ,Connection ID : 1976, LKMode : X .NONE, LKStatus : GRANTED, Command : [EXIT] insert into t1 values(16,'Skirt')
The other way is select the Lock time-out information from SYSLOCK, SYSTABLE, and SYSYUSER.
For example，
dmSQL> update t1 set c2 = 'Hello' where c1 = 16;
ERROR (1102): [DBMaker] lock time-out: Object ID: 2.18.12, Connection ID: 1976, LKMode: X .NONE, LKStatus: GRANTED, Command: [EXIT] insert into t1 values (16,'Skirt')
Lock time-out occur, Object ID = 2.18.12 is occupied by 1976 and status is X (nobody can get Object: 2.18.12 until 1976 connection release), this cause subsequent can’t get correct information for DB and Lock time-out occur.
Analyze the reasons for lock time-out:
First, execute the following SQL statement and know what connection caught database resources.
dmSQL> select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER, TABLE_NAME, LK_NEW_MODE, LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSTEM.SYSLOCK, SYSTEM.SYSTABLE, SYSTEM.SYSUSER where HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and LK_GRAN! ='SYSTEM' and TABLE_NAME not like 'SYS%' order by 1, 2, 3, 4, 5, 6;
HOLD_LK_CONNECTION LOGIN_IP_ADDR LOGIN_HOST LOGIN_TIME USER_NAME TABLE_OWNER TABLE_NAME LK_NEW_MODE LK_GRAN LK_STATUS LK_CURRENT_MODE TIME_OF_SQL_CMD
SQL_CMD
=======================================================================
1976 127.0.0.1 LA-WANGXUEWEI 2010/06/12 14:59:52 SYSADM SYSADM T1 NONE.NONE TABLE GRANTED IX .NONE 2010/06/12 15:01:00 [EXIT] insert into t1 values(16,'Skirt')
1976 127.0.0.1 LA-WANGXUEWEI 2010/06/12 14:59:52 SYSADM SYSADM T1 NONE.NONE TUPLE GRANTED X .NONE 2010/06/12 15:01:00 [EXIT] insert into t1 values(16,'Skirt')
1976 127.0.0.1 LA-WANGXUEWEI 2010/06/12 14:59:52 SYSADM SYSADM T1 NONE.NONE PAGE GRANTED IX .NONE 2010/06/12 15:01:00 [EXIT] insert into t1 values(16,'Skirt')
3868 127.0.0.1 LA-WANGXUEWEI 2010/06/12 14:59:56 SYSADM SYSADM T1 NONE.NONE TABLE GRANTED IX .NONE 2010/06/12 15:01:50 [EXEC] select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER,TABLE_NAME, LK_NEW_MODE, LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSTEM.SYSLOCK, SYSTEM.SYSTABLE, SYSTEM.SYSUSER where HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and LK_GRAN !='SYSTEM' and TABLE_NAME not like 'SYS%' order by 1,2,3,4,5,6
3868 127.0.0.1 LA-WANGXUEWEI 2010/06/12 14:59:56 SYSADM SYSADM T1 NONE.NONE PAGE GRANTED IU .NONE 2010/06/12 15:01:50 [EXEC] select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER,TABLE_NAME, LK_NEW_MODE, LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSTEM.SYSLOCK, SYSTEM.SYSTABLE, SYSTEM.SYSUSER where HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and LK_GRAN !='SYSTEM' and TABLE_NAME not like 'SYS%' order by 1,2,3,4,5,6
We found connection ID 1976 lock the resource by executing command: insert into t1 values (16,'Skirt').
Next, execute following SQL statement to get more detailed information for connection 1976 and Object 2.18.12.
dmSQL> select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER, TABLE_NAME, LK_NEW_MODE,
LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSLOCK, SYSTABLE, SYSTEM.SYSUSER where
HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and HOLD_LK_CONNECTION=1976 and LK_OBJECT_ID='2.18.12' order by
1, 2, 3, 4, 5, 6;
HOLD_LK_CONNECTION LOGIN_IP_ADDR LOGIN_HOST LOGIN_TIME USER_NAME TABLE_OWNER TABLE_NAME LK_NEW_MODE LK_GRAN LK_STATUS LK_CURRENT_MODE TIME_OF_SQL_CMD
SQL_CMD
=======================================================================
1976 127.0.0.1 LA-WANGXUEWEI 2010/06/12 14:59:52 SYSADM SYSADM T1 NONE.NONE TUPLE GRANTED X .NONE 2010/06/12 15:01:00 [EXIT] insert into t1 values(16,'Skirt')
This example to illustrate, the state of connection 1976 is [EXIT] insert into t1 values (16,'Skirt'), it denotes SQL command had been executed completely but doesn’t release resource. Reason may be summarized as two: one is the program hasn’t committed, the other is connection broken, this caused subsequent commit commands execute failed and make database server to a waiting status. Because of above reasons resource 2.18.12 can’t release all the while. You can execute kill connectioncommand to force resource release.
Overall, you can obtain the information from above two ways and start to analyze the reasons for Lock time-out, you should know the connection status which occupies the resource and busy or idle. If the behavior is normal, it will be released the resource after execution completely. And if the connection status is idle, you should execute “kill connection HOLD_LK_CONNECTION number” and release the resource.
2) The solution when LOCK TIME-OUT occurs
In order to improve the concurrency degree for system and reduce the waiting time for each transaction can adopt the following methods:
The first one way, shorten the transaction length and let each transaction accomplish in shortest time as soon as possible.
Second, reduce lock granularity and use tuple lock as possible. (In DBMaker, the default lock granularity level is row)
Third, use index san.Multiple transactions scan a same table with different indexes maybe improve the concurrency degree.
The last one, reduce the isolation level for transaction on condition that no affect for the validity of transaction. (DBMaker default lowest level is: read un-committed)
Version: DBMaker 5.x
Product: Normal, Bundle
Platform: Windows