Re: MySQL ResultSet - count rows?

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.

Even at "read uncommitted" isolation you likely will be fine. It depends on whether you need to see rows added during the transaction or not, i.e., on whether you want the values to represent the db state at the end of your transaction, or if it's sufficient to see the state at the beginning of the transaction.

After all, rows could be added a nanosecond after your transaction, too, and you wouldn't know it.

There's nothing wrong with seeing the db state as of transaction start, in most cases. There will *always* be latency in your queries. The point of the transaction is that it gives you a consistent view of the data, and that's almost always sufficient.

Bear in mind that not all configurations of MySQL tables support transactions.

--
Lew
.

Relevant Pages

  • Re: Oracle behaving strangely with multiple commands executing in a tight loop
    ... transaction: if a transaction with DML statements is executed, ... Do you execute the insert and update in a single transaction? ... My .NET blog: http://weblogs.asp.net/fbouma ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Oracle behaving strangely with multiple commands executing in a tight loop
    ... transaction: if a transaction with DML statements is executed, ... Do you execute the insert and update in a single transaction? ... Would we have to execute all commands within a single transaction, or just each individual INSERT INTO/UPDATE combo? ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Guarantee Transaction Isolation
    ... What you have will work with SERIALIZABLE isolation, but will not with the default READ COMMITED isolation. ... you can use either TABLOCKX or HOLDLOCK locking hints. ... > I would use a TABLOCK lock hint in the select, and ensure that I kept my transaction as short as possible. ... >> ShowNumber SMALLINT NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: MySQL ResultSet - count rows?
    ... SELECT COUNTapproach will only be guaranteed to work if you execute ... both SELECTs in a single transaction AND use SERIALIZABLE isolation ...
    (comp.lang.java.databases)
  • Re: MySQL ResultSet - count rows?
    ... SELECT COUNTapproach will only be guaranteed to work if you execute ... both SELECTs in a single transaction AND use SERIALIZABLE isolation ... the isolation level is REPEATABLE READ or lower. ...
    (comp.lang.java.databases)