GBase 8s常见锁错误原因及处理办法

错误原因：不能在表的数据行上加锁，该行数据已被锁 示例： session1

dbaccess testdb -					
drop table if exists t;
create table t(col1 int,col2 char(100));
insert into t select tabid,tabname from systables;		
begin;
delete from t where col1=1;		

session2

dbaccess testdb -					
begin;
delete from t where col1=1;		

-243: Could not position within a table (gbasedbt.t).
-107: ISAM error:  record is locked.

解决办法： 1、设置锁等待时间，等待被锁定的数据行锁释放（set lock mode to wait 10;）

-244 Could not do a physical-order read to fetch next row.

错误原因：表顺序扫描到某一个已被其他会话锁定的数据行出现该错误 示例： session1

dbaccess testdb -					
drop table if exists t;
create table t(col1 int,col2 char(100));
insert into t select tabid,tabname from systables;		
begin;
delete from t where col1=1;		

session2

dbaccess testdb -					
begin;
select * from t where col1=10;

-244    Could not do a physical-order read to fetch next row.
-107    ISAM error: record is locked.

解决办法： 1、创建合理索引，避免顺序扫描 2、设置锁等待时间，等待被锁定的数据行锁释放（set lock mode to wait 10;）

-245 Could not position within a file via an index.

错误原因：索引key被锁 示例： session1

dbaccess testdb -					
drop table if exists t;
create table t(col1 int,col2 char(100));
insert into t select tabid,tabname from systables;	
create index idx_t on t(col1);	
begin;
delete from t where col1=1;		

session2

dbaccess testdb -					
begin;
select * from t where col1=1;

-245: Could not position within a file via an index.
-144: ISAM error: key value locked.

解决办法： 1、设置锁等待时间，等待被锁定的key释放（set lock mode to wait 10;）

-246 Could not do an indexed read to get the next row.

错误原因：通过索引扫描定位到的数据行被锁 示例： session1

dbaccess testdb -					
drop table if exists t;
create table t(col1 int,col2 char(100));
insert into t select tabid,tabname from systables;	
create index idx_t on t(col1);	
begin;
update t set col2='aaa' where col1=1;

session2

dbaccess testdb -					
begin;
select * from t where col1=1;

-246    Could not do an indexed read to get the next row.
-107    ISAM error: record is locked.

解决办法： 1、设置锁等待时间，等待被锁定的数据行释放（set lock mode to wait 10;）

总结：

要避免数据库出现频繁的锁冲突，需要尽量避免表的全表扫描以及设置锁等待时间，创建合理的索引及设置合理的锁等待时间。 对于JDBC，可在URL添加环境变量设置锁等待：IFX_LOCK_MODE_WAIT=10; 对于其他应用程序，如不方便添加set lock mode to wait 10;设置会话级别锁等待，可考虑创建sysdbopen函数，设置全库有效的锁等待： dbaccess testdb <<! create procedure public.sysdbopen() set lock mode to wait 10; end procedure; !