|
Let check the problem with deadlock.
I made a small program fastly.
file insert.c
#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
int insert (int table, int value);
void usage (char *progname){
fprintf(stderr,
"usage: %s -i <instance> -d <dbname> [ -n amount of insertions, default 1 ]\n",
progname);
}
int main (int argc, char **argv){
int c; /* for getopt and a counter for cycle */
char instance[20] = "";
char dbname[20] = "";
char env[12+20];
int amount = 1;
int f; /* file descriptor */
unsigned short buf;
ssize_t rd;
if(argc < 3) usage(argv[0]), exit(1);
while((c = getopt(argc, argv, "i:d:n:")) != -1){
switch(c){
case 'i':
strncpy(instance, optarg, 20 - 1);
break;
case 'd':
strncpy(dbname, optarg, 20 - 1);
break;
case 'n':
if(!(amount = atoi(optarg))){
fprintf(stderr, "wrong -n argument - '%s'\n", optarg);
usage(argv[0]);
}
break;
default:
usage(argv[0]);
exit(1);
break;
}
}
if(instance[0] == '\0' || dbname[0] == '\0'){
usage(argv[0]);
exit(1);
}
sprintf(env, "DB2INSTANCE=%s", instance);
putenv(env);
f = open("/dev/urandom", O_RDONLY);
if(f == -1){
perror("Could not open /dev/urandom\n");
exit(1);
}
if(connect(dbname)) exit(1);
for(c = 0; c < amount; c++){
rd = read(f, &buf, 2);
if(rd == -1){
perror("Could not read from /dev/urandom\n");
exit(1);
}
if(buf < 0 ) buf *= -1;
printf("%d\n", buf);
/* add here a check for returned value, if it is equal -911
* we got a deadlock or lock timed out,
* we can repeat a transaction */
if (buf >= 0 && buf < 6553) insert (0, buf);
else if (buf >= 6553 && buf < 13106) insert (1, buf);
else if (buf >= 13106 && buf < 19659) insert (2, buf);
else if (buf >= 19659 && buf < 26212) insert (3, buf);
else if (buf >= 26212 && buf < 32765) insert (4, buf);
else if (buf >= 32765 && buf < 39318) insert (5, buf);
else if (buf >= 39318 && buf < 45871) insert (6, buf);
else if (buf >= 45871 && buf < 52424) insert (7, buf);
else if (buf >= 52424 && buf < 58977) insert (8, buf);
else if (buf >= 58977 && buf < 65535) insert (9, buf);
}
}
file tinsert.sqc
#include <stdio.h>
#include <sql.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlca.h>
EXEC SQL INCLUDE SQLCA;
int CHECK_OPER(struct sqlca *ptr, int *sqlcode) {
int rc = 0;
char errMessage[1024];
if (ptr->sqlcode == 0 || ptr->sqlcode == 100 )
return 0;
else{
if(ptr->sqlcode < 0){
strcpy(errMessage, "Error message: ");
}else
strcpy(errMessage, "Warning message: ");
fprintf(stderr, errMessage);
sprintf(errMessage, "SQLCODE = %ld\n", ptr->sqlcode);
}
fprintf(stderr, "got %s\n", errMessage);
rc = sqlaintp( errMessage, 1024, 80, ptr);
fprintf(stderr, "rc %d\n", rc);
if(rc > 0)
fprintf(stderr, "got %s\n", errMessage);
rc = sqlogstt( errMessage, 1024, 80, ptr->sqlstate);
fprintf(stderr, "rc %d\n", rc);
if(rc > 0)
fprintf(stderr, "got %s", errMessage);
if(ptr->sqlcode < 0){
return 1;
}else
return 0;
}
int connect (char *dbname){
int sqlcode;
EXEC SQL BEGIN DECLARE SECTION;
char dbAlias[20];
EXEC SQL END DECLARE SECTION;
strncpy(dbAlias, dbname, 20 - 1);
EXEC SQL CONNECT TO :dbAlias;
if (CHECK_OPER(&sqlca, &sqlcode)) return 1;
return 0;
}
int insert( short table, short value ){
int sqlcode;
EXEC SQL BEGIN DECLARE SECTION;
short doc_id;
short node_id;
EXEC SQL END DECLARE SECTION;
doc_id = value;
node_id = table;
EXEC SQL insert into t_v (doc_id, node_id) values(:doc_id, :node_id);
if (CHECK_OPER(&sqlca, &sqlcode)){
EXEC SQL ROLLBACK;
return sqlcode;
}
EXEC SQL COMMIT;
return 0;
}
Here is a db schema:
#!/bin/sh
. /export/home/db2inst1/sqllib/db2profile
db2 connect to test;
for i in 0 1 2 3 4 5 6 7 8 9
do
echo $i
db2 CREATE TABLE t$i \( \
DOC_ID SMALLINT NOT NULL,\
NODE_ID SMALLINT NOT NULL,\
T TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, \
CONSTRAINT t1_NODE_CHK \
CHECK \(NODE_ID = $i\)\
ENFORCED \
ENABLE QUERY OPTIMIZATION\
\);
db2 -v create index idx_${i}t on t$i \(T\) allow reverse scans;
done
db2 CREATE VIEW t_v AS \
SELECT \* FROM t0 UNION ALL \
SELECT \* FROM t1 UNION ALL \
SELECT \* FROM t2 UNION ALL \
SELECT \* FROM t3 UNION ALL \
SELECT \* FROM t4 UNION ALL \
SELECT \* FROM t5 UNION ALL \
SELECT \* FROM t6 UNION ALL \
SELECT \* FROM t7 UNION ALL \
SELECT \* FROM t8 UNION ALL \
SELECT \* FROM t9;
Let now make a test. A test box is an old one, 1996 year production, 2x300MHz.
time ./insert -i db2inst1 -d test -n 10000
real 1m26.775s
user 0m1.610s
sys 0m1.170s
we have about 116 transactions per second.
in anothe xterm let run db2 "select count(*), node_id from t_v group by node_id"
in a while(1) cycle, and again start
time ./insert -i db2inst1 -d test -n 10000 > /dev/null 2>&1
real 1m29.503s
user 0m1.390s
sys 0m0.690s
so we got about 112 transaction per second and no one deadlock.
It's not good.
Ok, let start a second
time ./insert -i db2inst1 -d test -n 10000
Now we have two clients doing insertion non-stop, ASAP.
Let get some stat at the same time by running
db2 "select sum(doc_id) from t_v where t >= (current_time - 30 seconds)"
We have now three clients, two doing insertion, one doing select, all three works asap and non-stop,
and no any errors nor deadlock.
Let try nselect sum(doc_id) from t_v - the result is the same.
Ok, let try select doc_id, node_id from t_v where t >= (current_timestamp - 1 second)
in another words get all rows last second inserted.
Again there is no deadlock - it just works. I have seen about 270 rows fetched (it's about transactions
per second from both clients doing insertion).
The same result is if we run
db2 update t_v set doc_id=doc_id+1 where doc_id!=9 and t>=(current_timestamp -1 second)
Unfortunatelly, I could not get a deadlock, having four clients working simultaneously.
The final test:
first client doing select for rows inserted in the last second in cycle without interval;
second client doing update doc_id for rows inserted in the last second in cycle again;
third and fourth doing insert ASAP continuosly;
For such old box, with everything are placed on a single hard disk, probably it is not bad.
|
