Commit Selects
Hi all,
In my WebApps i use the following database:
MySQL: 4.0
Type: InnoDB
Isolation Level: Repeatable Read
Today i read in the manual of MySQL this:
If you are running with the default REPEATABLE READ isolation level, all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.
Until now i use commit/rollback only for insert,update,delete operations.
But if i understand the article rightly, it means that its neccessary to make commits also after selects.
If I do not commit a select, a following request which gets the same connection from the pool, whould see only a snapshot of previous request and not the current data?
Did anybody execute selects in a manner like below?
Think rollback is not neccessary, because no data are changed.
try
{
conDB.setAutoCommit(false);
statement = conDB.prepareStatement("select ...");
set = statement.executeQuery();
...
}
finally
{
conDB.commit();
}
thanx,
J. Hammoud
[1474 byte] By [
hammouda] at [2007-10-2 12:13:55]
Committing after every SELECT isn't going to do what you want... You either need to change isolation levels, commit BEFORE each SELECT (or perhaps just when you get the connection from the pool), and/or possibly get a greater understanding of transactionality in the database world.
If you do as you propose, you can have:
First, Thread 1 does:
get connection A from pool
SELECT count(*) FROM my_table;
COMMIT; // this is time X
return connection A to pool
Then, Thread 2 does:
get connection B from pool
INSERT INTO my_table ...
COMMIT;
return connection B to pool
Then Thread 3 does:
get connection A from pool
SELECT count(*) FROM my_table; // this is time Y
What will happen in the above is that the SELECT at time Y will see the number of rows in my_table that were present at time X, because that was when the last COMMIT on the connection was performed.
If you want the actual number of records at time Y, you either need to COMMIT before the select, or use READ COMMITTED isolation level. Which you choose depends on whether you really need REPEATABLE READ within your transactions or not. For many puposes, READ COMMITTED is the better default isolation level, but for some situations it will break your program.
> Until now i use commit/rollback only for
> insert,update,delete operations.
> But if i understand the article rightly, it means
> that its neccessary to make commits also after
> selects.> If I do not commit a select, a following request
> which gets the same connection from the pool, whould
> see only a snapshot of previous request and not the
> current data?
>
>
From a glance I would say that is the same way I interpret it. Honestly though try it out and see what happens. That would be the best way to see exactly what is going on.
>get a greater understanding of transactionality in the database world.
therefore i read the manual:)
or do you mean its not enough?
(some links?)
Your example was very good and its absolutly right!
Think i did not get to this case for my own.
In my apps every request need the database connection.
So i will make a previous global commit in the FrontController.
READ COMMITTED
Think this level (every snapshot gets current data), could get inconstent view of data, when one logic function runs more selects.
thanx,
J. Hammoud
Not to muddy the waters too much but a SELECT ... FOR UPDATE will require a commit or rollback after the transaction is complete to release the locked rows, pages or tables that FOR UPDATE is holding.- Saish
Yes thats right, and i indeed consider currently about to do this correctly.
I search for a default handling, which i can easy use in every future app.
Up to now i think the only way to make this is to handel it at begin and end of the request.
Let us look to follwing case:
- get connection
- commit
- start special business logic
-- select for update
-- update
-- select all objects to list them
-- commit/rollback
- additional selects for navi and sidebar
- close connection
Is also not correct?
The additional selects, will be executed in an other Transaktion, than the select of the logic specific objects.
Could be not consistent if this data are depended to the logic objects.
So - currently i think - put commit/rollback to the end of the request.
But what, don't no if it could happen in future, an application need to commit directly after business logic.
Now i know that handling the commit is very neccessary, but don't see any good solution to manage this.
*confused*
cu
J. Hammoud
Seems very inefficient to me. Remember, a database connection is a scarce resource. Normally, your application server will offer more simultaneous connections than your database server, hence 'scarce resource'. This is one reason why connection pools (such as Jakarta's DBCP) are used, the other being the latency or cost associated with making a database connection in the first place. So, definitely consider pooling when you end up with your persistence strategy.
Here is an example of where SELECT FOR UPDATE is used:
> Borrow connection from pool
> SELECT ... FOR UPDATE on, say, a compound primary key where the last portion of the key is an incrementing value (common in legacy databases)
> Add one to the final key to derive the compound key for a new record
> INSERT the new record
> Commit (if successful) or rollback (on error)
> Return connection to the pool
All your business logic should have already occurred. You should only use a connection for what is known as a 'logical unit of work' or transaction. You should also try to hold onto that connection for the minimum possible amount of time, as the connection is a scarce resource. The above applies not only to connections, but to PreparedStatement, ResultSet, etc. These are also implicitly connected to database resources.
Another point, you only use SELECT ... FOR UPDATE in limited circumstances. Calculating the next value for a compound key is one case. Another is uploading binary or text data into a LOB database column (or downloading existing data).You don't want to always use SELECT ... FOR UDPATE because this causes a 'pessimistic locking' strategy on the database. Other requests on that table with block (wait) until the lock is cleared via a commit or rollback. For the vast majority of SELECT statements, you do not want this overhead. It would cause a scaling bottleneck.
Hope that helps.
- Saish
Also, you do not need to commit or rollback either when you connect manually or if you borrow a connection from the pool. It is a wasted database call. Some databases in certain transaction modes require a BEGIN TRANS or similar call, but unless this is explicitly needed (and normally it is not), do not make calls that are not needed.
- Saish
