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

-243 Could not position within a table table-name.

错误原因:不能在表的数据行上加锁,该行数据已被锁
示例:
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;)

总结:

要避免数据库出现频繁的锁冲突,需要尽量避免表的全表扫描以及设置锁等待时间,创建合理的索引及设置合理的锁等待时间。

标签: 锁错误, , 243, 245, 244, 246, 107

添加新评论