select count then insert

[lewindletter]

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

[Benjamin Gufler]

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

[jefftyzzer]

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

[lewindletter]

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

[Benjamin Gufler]

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

[Brian Tkatch]

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.

[Tonkuma]

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;

[Brian Tkatch]

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.

[ChrisC]

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.

[Lennart]

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

[jefftyzzer]

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.

[jarl]

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

[Benjamin Gufler]

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

[Brian Tkatch]

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.

[Brian Tkatch]

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.