GBase8s游标稳定性读
基础库表建立
create database db03 with log;
create table customer(
fname char(9),
lname char(9)
);
insert into customer values('a','A');
insert into customer values('b','B');
insert into customer values('c','C');
insert into customer values('aa','AA');
insert into customer values('bb','BB');
alter table customer lock mode (row);
游标稳定性读取行
#include <stdio.h>
#include <unistd.h>
EXEC SQL define FNAME_LEN 15;
EXEC SQL define LNAME_LEN 15;
main()
{
EXEC SQL BEGIN DECLARE SECTION;
char fname[ FNAME_LEN + 1 ];
char lname[ LNAME_LEN + 1 ];
EXEC SQL END DECLARE SECTION;
printf( "DEMO1 Sample ESQL Program running.\n\n");
EXEC SQL WHENEVER ERROR STOP;
EXEC SQL connect to 'db03';
EXEC SQL DECLARE democursor cursor for
select fname, lname
into :fname, :lname
from customer
where lname < 'C' ;
EXEC SQL execute immediate 'BEGIN WORK';
EXEC SQL execute immediate 'SET ISOLATION TO CURSOR STABILITY';
EXEC SQL open democursor;
for (;;)
{
getchar();
EXEC SQL fetch democursor;
if (strncmp(SQLSTATE, "00", 2) != 0)
break;
printf("%s %s\n",fname, lname);
}
if (strncmp(SQLSTATE, "02", 2) != 0)
printf("SQLSTATE after fetch is %s\n", SQLSTATE);
EXEC SQL execute immediate 'COMMIT WORK';
EXEC SQL close democursor;
EXEC SQL free democursor;
EXEC SQL disconnect current;
printf("\nDEMO1 Sample Program over.\n\n");
}
说明: 执行cs.exe程序,每按一次“回车”,程序的游标就会指向一行数据,此时这行数据就会被加上共享锁,别人就不能修改这行数据了。当游标指向下一行数据时,前面的一行数据就可以修改了。
测试步骤:
结果 | 会话1: | 会话2: | 窗口3: |
---|---|---|---|
./cs.exe | |||
“回车” | |||
onstat -g sql 可以看到CURSOR STABILITY隔离级别。 onstat -k 可以看到只有第1行锁定了,其他行没有锁定。 | |||
锁报错update执行失败 | update customer set lname=‘A1’ where fname=‘a’ ; | ||
update执行成功 | update customer set lname=‘B1’ where fname=‘b’ ; | ||
“回车” | |||
onstat -g sql 可以看到CURSOR STABILITY隔离级别。 onstat -k 可以看到只有第2行锁定了,其他行没有锁定。 | |||
update执行成功 | update customer set lname=‘A2’ where fname=‘a’ ; | ||
锁报错update执行失败 | update customer set lname=‘B2’ where fname=‘b’ ; |