Can a ResultSet be read dynamically?
In a case if there are 20 rows in a table EMP, and I executed the following code:-
Connection con=DriverManager.getConnection(URL);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("SQL code goes here...");
wait(5000);// call wait(5000) method to pause for a moment.
while(rs.next())
{
//some code here to retrieve result...
}
Now, in case 5 more rows are inserted/deleted/updated into/from the table EMP, while in wait(), then how many times will it iterate and fetch how many rows?
If only 20 rows are selected, then are there any ways to fetch the latest data?
Please clarify this for all the cases if we use different statement objects...
Thanks...
Message was edited by:
Bideep
# 1
> Now, in case 5 more rows are inserted/deleted/updated
> into/from the table EMP, while in wait(), then how
> many times will it iterate and fetch how many rows?
> If only 20 rows are selected, then are there any ways
> to fetch the latest data?
There is no way to tell for sure... I don't think it is a good idea to read data while another one is updating the same data, unless the number of rows you read is not relevant.
Why wait after opening the RS? This is not a good idea either, you should keep a RS open for the bare minimun!
# 2
> There is no way to tell for sure... I don't think it
> is a good idea to read data while another one is
> updating the same data, unless the number of rows you
> read is not relevant.
> Why wait after opening the RS? This is not a good
> idea either, you should keep a RS open for the bare
> minimun!
I disagree, I think there is a way to tell for sure. Data consistency is defined by the database you are using. If you use Oracle, you can be certain that you are always working with an image of data that is consistent to a single point-in-time. This is from the Oracle manual:
All Oracle SQL statements ALWAYS work with a image of data that is consistent to a single point-in-time. The consistent image consists of all changes and only changes COMMITTED before the point-in-time of the image.
The consistent image nevers shows changes that have not been committed to the database. Oracle never performs a "dirty read"
So, it is possible to to know the behavior of Oracle as you move forward. I would suggest that most if not all databases have a data consistency model that is well documented and allows you to know for certain what you can expect. It is expected and common to read data while it is updating, it is one of the reasons you would use a database rather then a flat file; it manages the locking mechanism for you so you don't shoot yourself in the foot. However, without understanding the underlying data consistency model you can easily corrupt the logical integrity of your database, although the better databases will have reasonable defaults designed to protect your data as best they can.
# 3
> I disagree, I think there is a way to tell for sure.
> Data consistency is defined by the database you are
> using.
Sure it is, you can tell for sure how many rows are in the table at any given time, but the number of rows you retrieve the way the OP does is not predictable mainly because you don't know if the new insert are after or before the current cursor position (what if the query contains a sort or a group by?).
Sure you have all kind of locking mechanism, still I don't think it is a good idea to keep a ResultSet open while you know that someone is putting rows which may (or may not) end up in the ResultSet itself.
Beside I don't think it is common to read a row WHILE it is updated, the db manages this (luckily) so you get the data before the update or after the update but you can't tell wich one you got, unless you force some kind of locking; it is a known concurrency issue, and it is one of the reasons why most people use a framework over JDBC to manage it.
And last there is not enough information from the OP, so my idea remains "you can't tell for sure".
P.S.: I agree with everything you write, but IMHO it is not enough for this particular case.
# 4
> And last there is not enough information from the OP,
> so my idea remains "you can't tell for sure".
> P.S.: I agree with everything you write, but IMHO it
> is not enough for this particular case.
I think you would make a great politician...
>still I don't think it is a good idea to keep a ResultSet
>open while you know that someone is putting rows
>which may (or may not) end up in the ResultSet itself.
This idea of avoiding concurrent read and update would stop people from updating any active table, which doesn't provide a practical approach to writing a database application.
When designing an application for a database, one must assume that one抯 data will be accessed and updated by multiple users in ways not originally conceived. One has to select a database that provides the appropriate locking mechanism. There are times when one may chose to implement something other then the default cursor types (some databases for example will allow dirty reads). But it is not a good idea to assume that one can simply code to avoid all possible locking or concurrency issues, one has to allow the database, which has been designed from the ground up to manage data consistency to do it抯 job. A programmer cannot manage data integrity / data consistency nearly as well as the databases can (1000抯 of programmers, millions of lines of code and dozens of years of analysis behind database data consistency).
I believe one should take full advantage of the database抯 ability to handle data consistency, and I do (still) believe that databases provide models of data consistency that effectively allows you to pre-determine the affects of concurrency and locking issues.
Lastly it is important to mention that an appropriate key structure is mandatory for effective concurrent access (avoiding unnecessary wait time and/or dead lock conditions) of database data. I could go on (and on) about this, but hopefully it is at least a bit self-explanatory.
I do agree with you that I抦 making some leaps of faith on exactly what type and level of information the original OP is looking for but hopefully this friendly discourse will at least give the OP something to consider.
# 5
> I think you would make a great politician...
What I intended to say is that I DO believe Oracle and the likes offer data consistency, otherwise I would just use flat files (believing in file system consistency!) :-)
My point about the example the OP posted is not about consistency, it is about how ResultSet are done: you can't have any guarantee that the data inserted after the RS opening are put beyond the associated cursor's current position, some may and some not (in general, in Oracle as you wrote the behaviour is in fact more predictable, but more expensive in terms of concurrency, so it is always wise to have a RS open for the least time possible), so you can't be sure (in general) on exactly how many rows you will retrieve. That is: you CAN guarantee that all rows will be there any time, but you are not guaranteed to retrieve them all scrolling an 'old' ResultSet; add to this the fact that the ResultSet usually cache some amount of data (they read, say, 100 rows and keep them in memory) and it gets even more complicated.
What do you think about it? We are not helping the OP or ourselves if we talk about two different matters.
And I meant no offense, I see you have much more experience in this forums than me, so you have all my respect; I' m talking serious, no kidding, if you felt sorry for something I wrote, I apologize, but it was not m intent to attack you.
# 6
> What I intended to say is that I DO believe Oracle
> and the likes offer data consistency, otherwise I
> would just use flat files (believing in file system
> consistency!) :-)
Thanks for clearing that up.
> My point about the example the OP posted is not about
> consistency, it is about how ResultSet are done: you
> can't have any guarantee that the data inserted after
> the RS opening are put beyond the associated cursor's
> current position, some may and some not (in general,
> in Oracle as you wrote the behaviour is in fact more
> predictable, but more expensive in terms of
> concurrency, so it is always wise to have a RS open
> for the least time possible), so you can't be sure
> (in general) on exactly how many rows you will
> retrieve. That is: you CAN guarantee that all rows
> will be there any time, but you are not guaranteed to
> retrieve them all scrolling an 'old' ResultSet; add
> to this the fact that the ResultSet usually cache
> some amount of data (they read, say, 100 rows and
> keep them in memory) and it gets even more
> complicated.
> What do you think about it?
Yes, you are probably correct, that is the question being asked and the answer is that a ResultSet is not dynamic, it is not a window into underlying "active" data, it is a snapshot of the data at the point in time you ran your query. It would appear that we both agree on that.
I don't know that the OP is familair with UNIX or not, but if you are it might help to think of a ResultSet as opening a file with vi which provides a static snapshot of a flat file. From your question as we think we understand it now, you were wondering if a ResultSet was similar to opening a flat file with tail -f, which it is not.
I don't think the fact that a database caches data complicates this, caching is just a means to an end; it provides snapshot capability with the added bonus of (generally) enhancing performance along with other data consistency functionality.
> We are not helping the OP
> or ourselves if we talk about two different matters.
Yeah, but we're trying...
> And I meant no offense, I see you have much more
> experience in this forums than me, so you have all my
> respect; I' m talking serious, no kidding, if you
> felt sorry for something I wrote, I apologize, but it
> was not m intent to attack you.
No offense taken at all, nothing wrong with you saying what you think and trying to help out a fellow forum member.
# 7
Actually keeping a result set open for along time may be a bad idea if you want the data to be latest.
Becouse while the result set is open there may be inserts, updates and werst of all deletions of records. If you want dirty reads for some reason you should be able to do that by changing the transaction isolation level in the connection (but the isolation level that you want has to be supported by the DBMS). But still it will read data that was there by the time of executing the query unless you use some dynamic result set.
The normal result set that you get by executing a sql statement is a static snapshot of the data at the time of executing the sql quesy in many DBMSs. If it is not you will get many more complicated issues when developing application programs.
If you want to make sure that data in the result set are the latest just add SQL locking code to lock the rows that you are quering when you do the query so no one will be able to change the data in DB until you are done with the data.
But locking and waiting can create major performance problems becouse of high lock durations.
One thing that you can do is in your initial query you only fetch the primary key column of the results and at later time fetch the rest of the columns on demand. This way you will get all the updates done after executing the inital query and if the records are deleted you will know about it. But that will have major performance limitations.
# 8
LRMK,
Good comments!
> Actually keeping a result set open for along time may
> be a bad idea if you want the data to be latest.
It is good practice to keep a ResultSet open for as short a time as possible because even if you only select it still locks resources in the database until you release it (commit or close ResultSet or Connection and this may be database dependent). I'm not quite sure if you were trying to make a point beyond what is common sense in getting the latest data (and there is nothing wrong with that, I just wasn't sure if you were saying something else).
> Becouse while the result set is open there may be
> inserts, updates and werst of all deletions of
> records. If you want dirty reads for some reason you
> should be able to do that by changing the transaction
> isolation level in the connection (but the isolation
> level that you want has to be supported by the DBMS).
> But still it will read data that was there by the
> time of executing the query unless you use some
> dynamic result set.
I agree with you here, as you say you can change the isolation level and read dirty data but it is still a point in time snapshot effective at the time of your SQL execution.
> The normal result set that you get by executing a sql
> statement is a static snapshot of the data at the
> time of executing the sql quesy in many DBMSs. If it
> is not you will get many more complicated issues when
> developing application programs.
Again, I抦 sorry, but I抦 a little unclear here. Are you saying that it is possible to do something other then a snapshot in a ResultSet or are you making the point that to attempt to do something other then a snapshot would be application folly (I agree with the latter).
> If you want to make sure that data in the result set
> are the latest just add SQL locking code to lock the
> rows that you are quering when you do the query so no
> one will be able to change the data in DB until you
> are done with the data.
This isn't the very best coding practice because you are attempting to supplant or supersede the data consistency model of the database. However, there are definitely times when lock for update must be done, I just believe that you should only explicitly lock (lock for update) when absolutely necessary. I might add that when lock for update is absolutely necessary there is probably something wrong with the schema design.
> But locking and waiting can create major performance
> problems becouse of high lock durations.
Ah, sorry, I didn't see this before I wrote my previous paragraph, but I left it for the surrounding comments. I think we are basically agreeing here?
WFF
# 9
> The normal result set that you get by executing a sql
> statement is a static snapshot of the data at the
> time of executing the sql quesy in many DBMSs. If it
> is not you will get many more complicated issues when
> developing application programs.
I mean this is how the result set works normally. But the point here is how it works is totaly up to the implementor of the driver and DBMS. If you are crazy enough you can make it work differently but that will definitly complicate things. All the DBMSs and the derivers that I know of works in the 'normal' way.
