W,
On 8/11/21 11:48, W wrote:
On Wednesday, August 11, 2021, 07:00:22 AM PDT, Christopher Schultz
wrote:
W, On 8/9/21 12:04, W wrote:>> Hi,I have a web application. It is a
java-jsp-tomcat-mysql. It is working, but sometimes, it is slow. For
each data update statement, it is not slow: the next jsp page shows
promptly. But the next page does not see updated data. I wait a coupe
seconds, refresh the page, the updated date shows. Each connection is
from tomcat.DataSource, so I believe each jsp page may use a
different database connection. So, mysql inside is slow. Anything I
can do?>> I checked server cpu and memory. Cpu is 20%, memory is
lower than 50%.> Everything is running on one server machine, one
tomcat, one mysql, runs on Ubuntu: 4.15.0-54-generic, Tomcat/9.0.16,
mysql: 8.0.21.>> Any information would be appreciated. Thanks in
advance.
This is certainly an application issue, so I have marked this
thread as "off-topic". That doesn't mean we can't help. Please
confirm the following: 1. An initial request arrives on the server
which executes a SQL DML query, which changes some data in your
database. The transaction is>committed. The response is sent to the
client.
>
The query is commited, otherwise I would not see updated data after I
refresh the next page.
So you steps 1 and 2 occur rapidly, you see old data, but if you RELOAD
the page shown in step 2, the data refreshes?
2. A second request arrives *after the completion of the first
request and queries the data which was changed in #1 above. The
data retrieved from the database appears to have values which are
"old" -- that is, the>value that was expected before #1 above
occurred.
>
Yes.
>
Some questions: * Are you sure that #2 happens fully after #1? How
are you sure?
Yes. Otherwise the second page would not appear.
You didn't say how you were sure. How does the user get from #1 to #2?
HTTP redirect?
* Are you absolutely sure you are querying the *same* data from
the database?
Yes.
Okay.
* Are you absolutely sure that there is no "step
1.5" where the data are>changed *back* to their original values?
How are you sure? Yes.
Okay.
* What is the "transaction isolation level"
of your database connections? Do you execute your query in #2
within a transaction?
>
I did not set, it should be default. REPEATABLE READ?
The JDBC default should be whatever the driver supplies, so it depends
upon your database driver.
I believe, correct me if I am wrong:tomcat.connection.commit() calls
mysql.connection.commit()
I don't know what tomcat.connection.commit() means. I also don't know
what mysql.connection.commit() means. You would need to provide sample
code including full class names and how you are obtaining various object
references.
If you are using either of Tomcat's built-in JDBC connection pools, then
calling java.sql.Connection.commit(() on the Connection object you
obtained from the JDBC connection pool should indeed call the MySQL
driver's commit() method.
Is it possible that actually
operating-system-file-writing not finished when the calls return?
No. Assuming you did something like this:
UPDATE user SET name='Chris' WHERE id=10;
COMMIT;
Then any subsequent thread which performs a query like this:
SELECT name FROM user WHERE id=1;
Should get the value 'Chris'. That's the whole contract that the
database enforces. Caching, buffering, etc. are not your concern; you
can rely on the database to implement things properly. (Well, unless you
are using MySQL 2.x or something like that.)
If you are seeing an "old" value for the user's name, it's due to one of
several possibilities:
a. Your SELECT happens before the COMMIT
b. Your SELECT happens in a transaction where you started the
transaction before the COMMIT was executed
c. You are caching data in your own application and no SELECT is being
executed
or
there is a delay, or updating query has lower priority
In relational databases, unless you try very hard, all writes are
synchronous. So once your Statement.execute() returns (if not in a
transaction) or Connection.commit() returns (if you are using a
transaction), then the data is written and visible to all threads,
connections, etc. This behavior is known as "Atomicity" and is the "A"
in the term "ACID" when referring to data-storage systems. It is very
closely related to "Consistency" which is the "C".
Atomicity requires that the effects of the whole transaction appear to
occur at the same time (or they fail and are rolled-back). Consistency
requires that all observers see the same thing (unless they are in
transactions that started before some related COMMIT).
either tomcat or mysql?
Tomcat passes your method calls directly to the driver. Tomcat itself
performs no JDBC operations when you make your calls.
The following connection call is a 'select' statement without delay,
so 'select' statement executed before operating-system-file-writing
finished. It sounds that you
