473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Question on select count()

I have a question on select count(), which may betray my lack of
database knowledge. Here goes.

I have java code driving a transaction which goes like this:

select count(*) from table

(Java code in the same transaction)
if count < 50 then ...

insert into table values(blahblah)
commit;

it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.

Am I getting this wrong ? Are there better solutions ?
Jul 19 '05 #1
7 10836
After exactly 51 inserts into a freshly created table, count will exceed 50.
:-)

I don`t see what you`re getting at.

Notes: Committing after each insert will slow down your application.
If you don`t commit, others won`t see the rows inserted. So
another process' "count" will not be affected, until you commit those
inserts.
You shouldn`t do a count iteratively in a loop, until 50 rows
have been inserted. Rather, count in your java code to keep trace of how
many you`ve inserted.

What are you trying to achieve?

Syltrem
"JP Belanger" <jp******@hotmail.com> a écrit dans le message de
news:b3**************************@posting.google.c om...
I have a question on select count(), which may betray my lack of
database knowledge. Here goes.

I have java code driving a transaction which goes like this:

select count(*) from table

(Java code in the same transaction)
if count < 50 then ...

insert into table values(blahblah)
commit;

it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.

Am I getting this wrong ? Are there better solutions ?

Jul 19 '05 #2
jp******@hotmail.com (JP Belanger) wrote in message news:<b3**************************@posting.google. com>...
I have a question on select count(), which may betray my lack of
database knowledge. Here goes.

I have java code driving a transaction which goes like this:

select count(*) from table

(Java code in the same transaction)
if count < 50 then ...

insert into table values(blahblah)
commit;

it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.

Am I getting this wrong ? Are there better solutions ?

If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...
Jul 19 '05 #3
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...


If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...


Are you saying I was unclear ? :)

I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.
Jul 19 '05 #4
jp******@hotmail.com (JP Belanger) wrote in message news:<b3**************************@posting.google. com>...
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...


If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...
Are you saying I was unclear ? :)

I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.


You mean, each session is registering itself in some table? Maybe you
can use v$session virtual table?
Another way is to create a table with fixed number of rows. Would
create a lot of headache: you need to update a row when session
is registering itself and update again when it's de-registering
(and you need a monitor which would detect dead session and
de-register them). Another problem with this design is that session needs
to connect to database to try to register itself...
The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.


Maybe not. Depends on what kind of operations do you do against the
database.
BTW, do you really need to restrict the number of sessions?
And, is it possible to use restrictions built-in into Oracle, like
restrictions on number of sessions, processes or transactions?
Jul 19 '05 #5

"JP Belanger" <jp******@hotmail.com> wrote in message
news:b3**************************@posting.google.c om...
af******@yahoo.com (Alex Filonov) wrote in message

news:<33**************************@posting.google. com>...


If you are trying to restrict number of rows to <= 50, there is something wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...


Are you saying I was unclear ? :)

I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

Why not just set the parameter in the init.ora file that restricts the
number of logons.
Very simple and reliable.
Jim
Jul 19 '05 #6
jp******@hotmail.com (JP Belanger) wrote in message news:<b3**************************@posting.google. com>...
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...


If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...


Are you saying I was unclear ? :)

I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

If you want a maximum number of session, just set the processes
parameter in init.ora appropiately.
If you want a maximum number of sessions per user, look up the CREATE
PROFILE command in the Oracle documentation.
You are now trying to hack yourself out, where the database can indeed
help you.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #7
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
You mean, each session is registering itself in some table? Maybe you
can use v$session virtual table?
I need to read up :)
Another way is to create a table with fixed number of rows. Would
create a lot of headache: you need to update a row when session
is registering itself and update again when it's de-registering
(and you need a monitor which would detect dead session and
de-register them). Another problem with this design is that session needs
to connect to database to try to register itself...
Essentially had the same thoughts you had about this one.
The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.
Maybe not. Depends on what kind of operations do you do against the
database.
BTW, do you really need to restrict the number of sessions?


Hehe. It was required for our application's licensing. Of course,
when you then ask what we should do with excess session, the answer,
as it very often is, was "let them go through anyways".
And, is it possible to use restrictions built-in into Oracle, like
restrictions on number of sessions, processes or transactions?


We may need to support many more sessions (100 000's). So I'll have
to read more on this.

Thanks for everybody's help.
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Ben Margolin | last post by:
I am new to subselects, and what I really want is to do them in an update, but the following shows a simpler select, that also doesn't seem to work as I think it should. Advice? Do I just...
4
by: Orion | last post by:
Hi, This is kind of last minute, I have a day and a half left to figure this out. I'm working on a project using ms-sqlserver. We are creating a ticket sales system, as part of the system, I...
2
by: Stéphane Cazeaux | last post by:
Hi I currently use pgsql 7.2.4 (but the following has also been seen on pgsql 7.3.3) with a transaction level set to "read committed". It do a lot of little tests to understand how concurrency...
1
by: Choop | last post by:
This may be dumb question, and i am sure there is an easy solution. anyway, here goes. I want to calculate values in a query. ie I want to find the count of records in a table that fit a...
9
by: Riley DeWiley | last post by:
I have a programming problem in OLEDB and C++ that seems to be pointing me toward using layered views and hierarchical rowsets. However, I am uncertain of the precise implementation and need...
17
by: Bob Lehmann | last post by:
Hi, My understanding is that Dispose() should not be used for destroying a connection object, and that Close() is preferred. However, in one of MS's Quickstart Apps I see this being used.... ...
13
by: groupy | last post by:
input: 1.5 million records table consisting users with 4 nvchar fields:A,B,C,D the problem: there are many records with dublicates A's or duplicates B's or duplicates A+B's or duplicates B+C+D's &...
6
by: ojorus | last post by:
Hi! My company make several flash-based games, and I use php to communicate with mysql to provide highscore-lists. My problem is this: When I save a player's score in the mysql-table, I want to...
3
by: bob laughland | last post by:
Hi All, I have a SQL query like this (I have tried to break the problem down to simplify it), select rowid from table where name in ('a', 'b', 'd') group by rowid Here is an example of data...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.