Re: index duplicate values, how much ?
Michael Schmid schreef: > > > > If you do updates/deletes , you may not want a seq scan since it locks > > the whole table.... > > > > Can you explain this further? if one updates a table and the database has no index to do the update, the database has to scan the whole table for ocurrances of the update; eq the where clause shoud be satisfied. doing this it needs to lock the whole table since someone else may come along and add a /update a record which may satisfy the where clause. That can not be allowed since you loose then the definition of a transaction. hope it clarifys above Superboer.
Well, think you are not right, consider this test:
The test runs in a database with unbuffered logging.
create table t (id integer, name varchar(20))
in testdbs
lock mode row;
insert into t(id, name) values (0, 'KARL');
insert into t(id, name) values (1, 'MAX');
insert into t(id, name) values (2, 'TOM');
insert into t(id, name) values (3, 'MIKE');
select * from t;
id name
0 KARL
1 MAX
2 TOM
3 MIKE
And now we start with two sessions:
Session 1:
set isolation to committed read;
set lock mode to not wait;
begin work;
update t
set name = name || 'X'
where id = 2;
Then, in Session 2:
set isolation to committed read;
set lock mode to not wait;
begin work;
insert into t(id, name)
values (2, 'TONI');
Now it might be interesting to look at onstat -K. I got the following
output:
Locks
address wtlist owner lklist same type tblsnum rowid
key#/bsiz
c096da8 0 c729438 0 c096e00 HDR+S 100002 204 0
c096e00 0 c72a350 0 0 S 100002 204 0
c096e58 0 c72a350 c096e00 c097010 HDR+IX 200034 0 0
c096eb0 0 c72a350 c096e58 0 *HDR+X 200034
103* 0
c097010 0 c729438 c096da8 0 IX 200034 0 0
c097068 0 c729438 c097010 0 *HDR+X 200034
105* 0
6 active, 2000 total, 2048 hash buckets, 0 lock table overflows
The table (tblsnum = 200034, rowid = 0) is not locked exclusive.
Only the two modified/inserted rows (rowid = 103/105) are locked exclusive.
And now we commit in both session (the order of the sessions is
irrelevant) with "commit work".
select * from t;
id name
0 KARL
1 MAX
2 TOMX
3 MIKE
*2 TONI*
try the insert first (leave trx open!!) then the update....;
do 2 updates, one with
where id = 0
the other where id = 1
... ;
etc.
Superboer.
Michael Schmid schreef:
> Well, think you are not right, consider this test:
>
> The test runs in a database with unbuffered logging.
>
> create table t (id integer, name varchar(20))
> in testdbs
> lock mode row;>
> insert into t(id, name) values (0, 'KARL');
> insert into t(id, name) values (1, 'MAX');
> insert into t(id, name) values (2, 'TOM');
> insert into t(id, name) values (3, 'MIKE');>
> select * from t;>
> id name
>
> 0 KARL
> 1 MAX
> 2 TOM
> 3 MIKE
>
> And now we start with two sessions:
>
> Session 1:
>
> set isolation to committed read;
> set lock mode to not wait;>
> begin work;
> update t
> set name = name || 'X'
> where id = 2;
>
> Then, in Session 2:
>
> set isolation to committed read;
> set lock mode to not wait;>
> begin work;
> insert into t(id, name)
> values (2, 'TONI');>
> Now it might be interesting to look at onstat -K. I got the following
> output:
>
> Locks
> address wtlist owner lklist same type tblsnum rowid
> key#/bsiz
> c096da8 0 c729438 0 c096e00 HDR+S 100002 204 0
> c096e00 0 c72a350 0 0 S 100002 204 0
> c096e58 0 c72a350 c096e00 c097010 HDR+IX 200034 0 0
> c096eb0 0 c72a350 c096e58 0 *HDR+X 200034
> 103* 0
> c097010 0 c729438 c096da8 0 IX 200034 0 0
> c097068 0 c729438 c097010 0 *HDR+X 200034
> 105* 0
> 6 active, 2000 total, 2048 hash buckets, 0 lock table overflows
>
> The table (tblsnum = 200034, rowid = 0) is not locked exclusive.
> Only the two modified/inserted rows (rowid = 103/105) are locked exclusive.
>
> And now we commit in both session (the order of the sessions is
> irrelevant) with "commit work".
>
> select * from t;>
> id name
>
> 0 KARL
> 1 MAX
> 2 TOMX
> 3 MIKE
> *2 TONI*
>
> --------------080103020405010806020800
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3388
>
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> <title></title>
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Well, think you are not right, consider this test:<br>
> <br>
> The test runs in a database with unbuffered logging.<br>
> <br>
> create table t (id integer, name varchar(20))<br>
> in testdbs<br>
> lock mode row;<br>> <br>
> insert into t(id, name) values (0, 'KARL');<br>
> insert into t(id, name) values (1, 'MAX');<br>
> insert into t(id, name) values (2, 'TOM');<br>
> insert into t(id, name) values (3, 'MIKE');<br>> <br>
> select * from t;<br>> <br>
> id name<br>
> <br>
> 0 KARL<br>
> 1 MAX<br>
> 2 TOM<br>
> 3 MIKE<br>
> <br>
> And now we start with two sessions:<br>
> <br>
> Session 1:<br>
> <br>
> set isolation to committed read;<br>
> set lock mode to not wait;<br>> <br>
> begin work;<br>
> update t<br>
> set name = name || 'X'<br>
> where id = 2;<br>
> <br>
> Then, in Session 2:<br>
> <br>
> set isolation to committed read;<br>
> set lock mode to not wait;<br>> <br>
> begin work;<br>
> insert into t(id, name)<br>
> values (2, 'TONI');<br>> <br>
> Now it might be interesting to look at onstat -K. I got the following
> output:<br>
> <br>
> Locks<br>
> address wtlist owner lklist same type tblsnum rowid
> key#/bsiz<br>
> c096da8 0 c729438 0 c096e00 HDR+S 100002
> 204 0<br>
> c096e00 0 c72a350 0 0 S 100002
> 204 0<br>
> c096e58 0 c72a350 c096e00 c097010 HDR+IX 200034
> 0 0<br>
> c096eb0 0 c72a350 c096e58 0 <b>HDR+X 200034 103</b>
> 0<br>
> c097010 0 c729438 c096da8 0 IX 200034
> 0 0<br>
> c097068 0 c729438 c097010 0 <b>HDR+X 200034 105</b>
> 0<br>
> 6 active, 2000 total, 2048 hash buckets, 0 lock table overflows<br>
> <br>
> The table (</tt><tt>tblsnum = </tt><tt>200034, rowid = 0) is not
> locked exclusive.<br>
> Only the two modified/inserted rows (rowid = 103/105) are locked
> exclusive.<br>
> </tt><tt><br>
> And now we commit in both session (the order of the sessions is
> irrelevant) with "commit work".<br>
> <br>
> select * from t;<br>> <br>
> id name<br>
> <br>
> 0 KARL<br>
> 1 MAX<br>
> 2 TOMX<br>
> 3 MIKE<br>
> <b>2 TONI</b></tt><br>
> </body>
> </html>
>
> --------------080103020405010806020800--
Superboer schrieb: > try the insert first (leave trx open!!) then the update....; > > do 2 updates, one with > where id = 0 > the other where id = 1 > ... ; > > etc. > > Superboer. > > Well, when you insert first (and leave this transaction open), you have an exclusive lock on that new row (*no* table lock). The second session executes an update statement and has to a full table scan. And in the course of this scan the session will come to this new row from the first session and will then wait for the first session to commit (or rollback) or throw an error (depending on the lock mode). And if the table would have an index on id - and if informix decides to use this index (instead of the full table scan) or you hint it do so -, the wait/error situation could be avoided. It's the same with two session, that both update: the first will update and the second will block on the *exclusive row lock* from the first. (with no index of course). But still there will be *no* exclusive *table* lock.