Reentrant locks and JDBC
I would like to ask something regarding JDBC connections and the need to use reentrant locks or not because i am confused. One of my teachers on the univ is claiming that i should use reentrant locks (readers-writer problem) when two threads are accessing the same database connection object to a MySQL database. I disagree with that and i want to learn the mechanics behind this problem, if it is applicable in the scenario described below.
Lets say i have a one to many relationship, eg one table for the users and one table for the user addresses. The user addresses table contains a FK to the user table.
We have the following scenario:
The server app has a administration control panel (swing JFrame) from which the administrator can delete users. Also, the same server app publishes a web service endpoint from which the users can add more addresses via SOAP. Thus i have at least one thread for the GUI and some threads for the Web service. The application is using only one connection object (via singleton pattern). Database connection pooling is not allowed. The users execute INSERT and SELECT to the addresses table and the administrator may delete users from the User table anytime. Of course, only the connection object is shared, not the statements.
If i synchronize the access to the database connection object then the access to the database is effectively serialized which is obviously a mistake. So, here are my questions:
a) Do i need a reentrant lock (on the connection object) to this scenario since the administrator is a writer and the users are readers/writers?
b) If I set CASCADE delete to the foreign key relationship between Users and Addresses, do i still need a reentrant lock? If so, then what is the worst scenario that could happen?
And here is my main concern, which he has avoided to answer. If a reentrant lock is indeed needed in this scenario, then why wouldn't the same kind of synchronization be needed if the GUI and the WebService where not on the same computer but they were different processes?
Is the problem only related to the connection object itself, or to the database ?