By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,302 Members | 1,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,302 IT Pros & Developers. It's quick & easy.

select count then insert

P: n/a
Hi

If I do

Begin transaction
Stmt 1: select count(*) from tableA
If the count is zero, then proceed to the following
Stmt 2: insert "something" to tableA

Stmt 3: insert "something" to tableB
End transaction

If I am using DB2 default setting, would there be data consistency
issue if there are two users
doing the above transaction. (i.e., both select, got count=0, then
proceed to insert)

Would an unique index help?

Thanks
LeWindLetter

Jan 16 '07 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Hi.

On 2007-01-16 10:28, le**********@gmail.com wrote:
If I am using DB2 default setting, would there be data consistency
issue if there are two users
doing the above transaction. (i.e., both select, got count=0, then
proceed to insert)
That depends on the transaction isolation level you're using. With the
default setting, "Cursor Stability", you will run into problems.
Have a look at the different isolation levels that are available, and
the "set transaction isolation" command.
Would an unique index help?
No, it wouldn't.

Benjamin
Jan 16 '07 #2

P: n/a
This is sort of tangential, but just in case it's helpful, you may want
to consider (if you haven't already) writing your conditional insert
like so:

BEGIN ATOMIC
IF EXISTS(SELECT 1 FROM TABLEA) THEN
INSERT INTO TABLEA...
ELSE
...
END IF
END;

or, perhaps, use MERGE

--Jeff

le**********@gmail.com wrote:
Hi

If I do

Begin transaction
Stmt 1: select count(*) from tableA
If the count is zero, then proceed to the following
Stmt 2: insert "something" to tableA

Stmt 3: insert "something" to tableB
End transaction

If I am using DB2 default setting, would there be data consistency
issue if there are two users
doing the above transaction. (i.e., both select, got count=0, then
proceed to insert)

Would an unique index help?

Thanks
LeWindLetter
Jan 16 '07 #3

P: n/a
To Benjamin,

Do you mean using Repeatable read isolation level? Is it on connection
level? If I am using a JDBC connection pool, would it affect other
transaction?
In this case, would select count lock the whole table?

To Jeff,

Is there a way to do it in Java rather than using PL SQL? cause I
don't want to tied to a particular database

Thanks both of you first

Jan 17 '07 #4

P: n/a
On 2007-01-17 07:33, le**********@gmail.com wrote:
Do you mean using Repeatable read isolation level?
I mean the level that is called "repeatable read" in DB2, but
"serializable" in common.
Is it on connection level?
Yes.
If I am using a JDBC connection pool, would it affect other
transaction?
I don't see any connection between the usage of a connection pool and
the behaviour of transactions. However, every transaction in your system
should use an appropriate isolation level. Not every operation requires
serializable isolation.
In this case, would select count lock the whole table?
I assume the select count(*) will cause a share lock on the whole table
and thus any other transaction running in isolation level serializable
will not be allowed to update or insert data in that table unless the
lock is released.

Benjamin
Jan 17 '07 #5

P: n/a

le**********@gmail.com wrote:
Hi

If I do

Begin transaction
Stmt 1: select count(*) from tableA
If the count is zero, then proceed to the following
Stmt 2: insert "something" to tableA

Stmt 3: insert "something" to tableB
End transaction

If I am using DB2 default setting, would there be data consistency
issue if there are two users
doing the above transaction. (i.e., both select, got count=0, then
proceed to insert)

Would an unique index help?

Thanks
LeWindLetter

Just a note.

Statement 1 and 2 can be combined into one statement:

INSERT INTO tableA SELECT something FROM (VALUES(something)) t
WHERE (SELECT COUNT(*) FROM tableA) = 0

B.

Jan 17 '07 #6

P: n/a
How about this?
(Columns are arbitrally specified.)
CREATE TABLE tableA
(col1 INTEGER
,col2 INTEGER
);

CREATE TABLE tableB
(genkey INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
,col1 INTEGER
,col2 INTEGER
);

WITH checkA AS (
select count(*) countA from tableA
)
,InsertA(DummyA) AS (
SELECT 0
FROM FINAL TABLE(insert into tableA
VALUES (7, 400) )
WHERE (SELECT countA FROM checkA) = 0
)
SELECT 0
FROM FINAL TABLE(insert into tableB
(col1, col2)
VALUES (7, 350) )
WHERE (SELECT countA FROM checkA) = 0;

Jan 18 '07 #7

P: n/a
Tonkuma wrote:
How about this?
(Columns are arbitrally specified.)
CREATE TABLE tableA
(col1 INTEGER
,col2 INTEGER
);

CREATE TABLE tableB
(genkey INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
,col1 INTEGER
,col2 INTEGER
);

WITH checkA AS (
select count(*) countA from tableA
)
,InsertA(DummyA) AS (
SELECT 0
FROM FINAL TABLE(insert into tableA
VALUES (7, 400) )
WHERE (SELECT countA FROM checkA) = 0
)
SELECT 0
FROM FINAL TABLE(insert into tableB
(col1, col2)
VALUES (7, 350) )
WHERE (SELECT countA FROM checkA) = 0;
Whoa!

Care to explain this? I do not understand "FINAL TABLE()" or how it can
have an INSERT inside it.

B.

Jan 18 '07 #8

P: n/a
Benjamin Gufler wrote:
On 2007-01-17 07:33, le**********@gmail.com wrote:
Do you mean using Repeatable read isolation level?

I mean the level that is called "repeatable read" in DB2, but
"serializable" in common.
Is it on connection level?

Yes.
You can also do this on a statement level. To do so, append a WITH RR
onto the end of the select statement - it will change the isolation
level for the duration of that query to Repeatable Read.

Jan 18 '07 #9

P: n/a

Brian Tkatch skrev:
[...]
Whoa!

Care to explain this? I do not understand "FINAL TABLE()" or how it can
have an INSERT inside it.

B.
It is called transition tables (I think) and they are somewhat similar
to what you can do in a trigger (referencing ... as ...). From what I
understand they are not part of sql standard (yet), but I find them
extremely handy. For example, when deleting from a table you can select
from the effected rows, and if you see something that shouldn't be
deleted, rollback. I.e.

db2 +c "select * from old table (delete from T where ...) X"

-- oops, two many

db2 rollback

See example 7 at:

http://publib.boulder.ibm.com/infoce...65%20%28%22%20
/Lennart

Jan 18 '07 #10

P: n/a
Brian:

Take a peek at http://www.vldb.org/conf/2004/IND1P1.PDF. Pretty cool
stuff :-).

--Jeff

Brian Tkatch wrote:
Tonkuma wrote:
How about this?
(Columns are arbitrally specified.)
CREATE TABLE tableA
(col1 INTEGER
,col2 INTEGER
);

CREATE TABLE tableB
(genkey INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
,col1 INTEGER
,col2 INTEGER
);

WITH checkA AS (
select count(*) countA from tableA
)
,InsertA(DummyA) AS (
SELECT 0
FROM FINAL TABLE(insert into tableA
VALUES (7, 400) )
WHERE (SELECT countA FROM checkA) = 0
)
SELECT 0
FROM FINAL TABLE(insert into tableB
(col1, col2)
VALUES (7, 350) )
WHERE (SELECT countA FROM checkA) = 0;

Whoa!

Care to explain this? I do not understand "FINAL TABLE()" or how it can
have an INSERT inside it.

B.
Jan 18 '07 #11

P: n/a
Benjamin Gufler wrote:
On 2007-01-17 07:33, le**********@gmail.com wrote:
Do you mean using Repeatable read isolation level?

I mean the level that is called "repeatable read" in DB2, but
"serializable" in common.
Not knowing much about DB2, but is "repeatable read" really the same as
"serializable"? Aren't they are slightly different?
>From what I understand, serializable is the most secure isolation
level, which guarantees that the execution of the operations of
concurrently executing transactions produces the same effect as some
serial execution of those same transactions (i.e. an execution where
one transaction executes to completion before the next begins).

AFAIK repeatable read offers the same consistency guarantee as
serializable, except that phantoms may be encountered. (I.e. a
transaction reads a set of rows that satisfy a search condition.
Another transaction then performs an update which generates one or more
new rows that satisfy the search condition. If the original query is
repeated (using the same search condition), extra rows appear in the
result-set that where previously not found.)
/Jarl

Jan 19 '07 #12

P: n/a
On 2007-01-19 11:45, ja**@mimer.com wrote:
Benjamin Gufler wrote:
Not knowing much about DB2, but is "repeatable read" really the same as
"serializable"? Aren't they are slightly different?
No, they are not different. It's just the DB2 naming of the levels that
may cause some confusion:

"common" name | DB2 name
-----------------+----------------------
read uncommitted | uncommitted read
read committed | cursor stability
repeatable read | read stability
serializable | repeatable read

Benjamin
Jan 19 '07 #13

P: n/a
Lennart wrote:
Brian Tkatch skrev:
[...]
Whoa!

Care to explain this? I do not understand "FINAL TABLE()" or how it can
have an INSERT inside it.

B.

It is called transition tables (I think) and they are somewhat similar
to what you can do in a trigger (referencing ... as ...). From what I
understand they are not part of sql standard (yet), but I find them
extremely handy. For example, when deleting from a table you can select
from the effected rows, and if you see something that shouldn't be
deleted, rollback. I.e.

db2 +c "select * from old table (delete from T where ...) X"

-- oops, two many

db2 rollback

See example 7 at:

http://publib.boulder.ibm.com/infoce...65%20%28%22%20
/Lennart
Thanx for pointing it out. I'll have to read more about this later.

B.

Jan 19 '07 #14

P: n/a

jefftyzzer wrote:
Brian:

Take a peek at http://www.vldb.org/conf/2004/IND1P1.PDF. Pretty cool
stuff :-).

--Jeff
Thanx for the link. I'm going to have to read that.

B.

Jan 19 '07 #15

This discussion thread is closed

Replies have been disabled for this discussion.