[prev in list] [next in list] [prev in thread] [next in thread] 

List:       tomcat-user
Subject:    Re: [OT] Other connection may not see updated date immediately
From:       Christopher Schultz <chris () christopherschultz ! net>
Date:       2021-08-13 22:04:56
Message-ID: a58abb66-6160-d44e-9a3e-b2953db07162 () christopherschultz ! net
[Download RAW message or body]

W,

On 8/11/21 11:48, W wrote:
> On Wednesday, August 11, 2021, 07:00:22 AM PDT, Christopher Schultz
> <chris@christopherschultz.net> 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 did not hear similar issues.
Again, I'm going to have to either blame the application code or the 
(human) observer. Either the code isn't working the way you say it is, 
or you are misunderstanding what you are seeing.

It's not impossible for these to be a bug in your database (MySQL) or in 
the connection-pool (DBCP) or even in the JDBC driver (Connector/J?). 
But it is MUCH more likely that the problem is in your own code.

If you can share some code, maybe we can help further.

-chris

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]