473,887 Members | 2,378 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 10883
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******@hotma il.com> a écrit dans le message de
news:b3******** *************** ***@posting.goo gle.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 ?

Jul 19 '05 #2
jp******@hotmai l.com (JP Belanger) wrote in message news:<b3******* *************** ****@posting.go ogle.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.go ogle.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******@hotmai l.com (JP Belanger) wrote in message news:<b3******* *************** ****@posting.go ogle.com>...
af******@yahoo. com (Alex Filonov) wrote in message news:<33******* *************** ****@posting.go ogle.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******@hotma il.com> wrote in message
news:b3******** *************** ***@posting.goo gle.com...
af******@yahoo. com (Alex Filonov) wrote in message

news:<33******* *************** ****@posting.go ogle.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******@hotmai l.com (JP Belanger) wrote in message news:<b3******* *************** ****@posting.go ogle.com>...
af******@yahoo. com (Alex Filonov) wrote in message news:<33******* *************** ****@posting.go ogle.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.go ogle.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
2979
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 misunderstand how subselects are actually executed? (This is on mysql version 4.1.0-alpha-max-nt.) First, here's the tables in question: mysql> describe m;
4
2664
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 need to be able to do a search for specific tickets withing price ranges, different locations within the theaters, etc. etc. My problem is in the search one of the criteria is to search for a group of seats together. For example let's say...
2
1974
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 control works. Let see this scenario: We have a table named "test_count" and a field named "count" The table contains 1 entry with count=1
1
296
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 criteria that I can specify in a sql subquery. Here is what I have, hopefully it is clear enough.
9
2013
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 guidance from someone experienced in those areas. The problem, in the abstract, is how to select a set of records in one table, each one of which has a relation to every one of a set of records in another table, the tables being joined in a...
17
1707
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.... This is found in the SqlHelper class - Dim cn As New SqlConnection(connectionString)
13
1920
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 & so on. Mathematicly there are 16-1 posibilities for each duplication. aim: find the duplicates & filter them, leave only the unique users which don't have ANY duplication. We can do it by a simple select query that logicly checks the
6
1506
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 find which place the player got with his score (today). To get this I have tried two different solutions, which both works, but are very ineffective: (The Time-field is a DateTime type, and I have Score and Time as Indexes)
3
2319
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 in the table rowid name
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10875
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10432
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7141
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6011
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4632
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.