Re: MySQL ResultSet - count rows?

Lew <lew@xxxxxxxxxxxxx> writes:

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.

I don't know how it works in MySQL, but in Java DB/Derby, you will if
the isolation level is REPEATABLE READ or lower. The following
example will print 'T has 3 rows' and then list all 6 rows if c1
executes with REPEATABLE READ. With SERIALIZABLE, the second connection
gets a lock timeout when trying to insert the last 3 rows.

Connection c1 = DriverManager.getConnection(args[1]);
Statement s1 = c1.createStatement();
try { s1.execute("drop table T"); } catch (SQLException e) {}
s1.execute("create table T(i int unique not null, j int)");
s1.execute("insert into T values (0, 0), (1, 1), (2, 2)");

c1.setAutoCommit(false);
c1.setTransactionIsolation(args[2].equals("serializable")?
Connection.TRANSACTION_SERIALIZABLE:Connection.TRANSACTION_REPEATABLE_READ);
ResultSet count = s1.executeQuery("select count(*) from T");
count.next();
System.out.println("T has "+count.getInt(1)+" rows");

Connection c2 = DriverManager.getConnection(args[1]);
Statement s2 = c2.createStatement();
s2.execute("insert into T values (5, 5), (6, 6), (7, 7)");

ResultSet rows = s1.executeQuery("select * from T");
while (rows.next()) {
System.out.println(rows.getInt(1)+", "+rows.getInt(2));
}



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.


--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html
.

Relevant Pages

  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... is possible for others to add other new orders to the database which I will ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction Isolation Level
    ... New rows must not be added to either table A and B while my transaction ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: transactions on reads?
    ... in the Books Online under the topic "Cursor Transaction ... Isolation Levels" it ... > column in the Invoice table, so there will never be any logical issue ...
    (microsoft.public.sqlserver.programming)
  • Re: Using transactions to insert into to a table while allowing re
    ... that is a fascinating solution. ... could change the default transaction isolation level for the table, ... >>colA int not null ...
    (microsoft.public.sqlserver.programming)