Re: MySQL ResultSet - count rows?

Arne Vajhøj wrote:
Lew wrote:
Dyreatnews@xxxxxxx wrote:
Ruud is making a very good point. I've just been told that the the
SELECT COUNT(*) approach will only be guaranteed to work if you execute
both SELECTs in a single transaction AND use SERIALIZABLE isolation
level. Even at REPEATABLE READ you may see new rows added since the
previous select, (but all rows counted will have the same value
throughout the transaction).

You won't "see" those new rows added until after the transaction completes.

You will.

user 1 - SELECT COUNT(*)
user 2 - INSERT
user 1 - SELECT

Only SERIALIZABLE will prevent this.

Arne

To make this decision a real dilemma, using serializable transactions will have 'interesting' consequences for resource contention, hence timeouts and (percieved) performance in any multi-user environment. This really is something to consider carefully.

Re-reading the original post, I saw that this is about writing a ResultSet's data into an Array. It 'd be much wiser to use some subtype of List in this case, and just avoid knowing the number of rows in advance. I think one of the other posters already suggested this. Doing so would keep one away from this minefield.

Ruud.
.

Relevant Pages

  • Re: Concurrent Connections / Shared Transaction
    ... In MARS there are fixed interleave points in the server. ... Do you mean that one will execute and ... bound sessions allow you to share the transaction space among ...
    (microsoft.public.dotnet.framework.adonet)
  • Urgent problem: Any help greatly appreciated
    ... I am trying to execute a number of SQL stored procedures in a single ... I really need to be able to execute these procedures in a single transaction ... SqlTransaction trans; ...
    (microsoft.public.dotnet.languages.csharp)
  • Urgent problem: Any help greatly appreciated
    ... I am trying to execute a number of SQL stored procedures in a single ... I really need to be able to execute these procedures in a single transaction ... SqlTransaction trans; ...
    (microsoft.public.dotnet.framework.adonet)
  • Urgent problem: Any help greatly appreciated
    ... I am trying to execute a number of SQL stored procedures in a single ... I really need to be able to execute these procedures in a single transaction ... SqlTransaction trans; ...
    (microsoft.public.dotnet.general)
  • Re: Concurrent Connections / Shared Transaction
    ... Do you mean that one will execute and ... bound sessions allow you to share the transaction space among ... > connections but doesn't give you concurrent access to it. ... > Server 2005 you can mix MARS plus asynchronous command execution to submit ...
    (microsoft.public.dotnet.framework.adonet)