Concurrency of JPA when same DB used by other applications

Question

I am developing a Spring MVC JPA web application. When this application is deployed in live the same DB that my application interacts with will be used by other 2 Dotnet and VB applications at the same time. I am managing the concurrency of my JPA app through version column.

Will there be any issue when running these 3 apps at the same time for the same database? All systems are using the same tables.

I will have to build another application (most probably Spring MVC + JPA) for the same DB in the future. Will there be any issues when running both apps at the same time (in terms of persisting the same tables in both apps etc.)?

Answer 1

Multiple applications accessing the same database tables can be (and usually is) a concurrency nightmare. Just adding a version column to tables does not help because each application may use different concurrency management mechanisms. Common problems encountered when sharing a database across applications (assuming read-write access for all and in no particular order):

1. Concurrency mismatch: Imagine one application using optimistic locking throughout, another using pessimistic locking and a third using no locking at all (since all are maintained by different teams). Even if there is a central architecture group handing out good application architecture advice to everyone, developers can do whatever they like and end up causing concurrency hell.
2. Deadlocks: Imagine one application using SERIALIZABLE isolation level for all transactions and performing long-running operations on the database, causing queues, deadlocks and timeouts. Even if other applications do not corrupt data, they may end up seeing too many errors due to deadlocks and timeouts, reducing their usefulness.
3. Data validity: It is common for developers to use in-memory caches for storing fixed or slow-changing data to save repeated database roundtrips. In a JPA application backed by Hibernate, developers could use a second-level cache. However, if another application updates the database, the cache would be holding stale (and therefore inaccurate) data.
4. Data integrity: Different applications may use different parts of the data. If all are allowed to update the common data independently, how is referential integrity maintained? What about business rules? Will they have to be duplicated across the applications?
5. Team communication overhead: Each team has to keep the other teams informed about changes they need to make to the schema so that they do not step on to each others' toes. This may even reduce agility if other teams do not agree with changes required by a team or if priorities cannot be aligned.
6. Schema errors: What happens if someone deletes, renames, moves or archives tables or columns required by an application?
7. Access control: If the underlying data is sensitive and requires authentication and authorization, access control checks will have to be duplicated across the applications.
8. Ownership: Who owns the common tables becomes a challenge as each team may have its own stakeholders, roadmap, constraints and priorities.
9. Portability: If the underlying database (vendor and type) has to be changed some day, all applications will need to be changed.
10. Auditing and versioning: If common data needs to be audited and/or versioned (multiple versions of data rows), the code will either have to be duplicated across applications or built into the database (which may not be easy within the database, for example, knowing which application user changed a record). If done within the database, portability is affected again because the syntax may vary from one database vendor to another.
11. Database-specific optimizations: Some scenarios (for example, reporting) may require native queries or other optimizations that may not be possible or too hard to perform using an ORM technology like JPA. If multiple applications require access to optimized queries, such functionality will either have to be built into the database (stored procedures) or duplicated across the applications (in possibly different ways).
12. Archival and partitioning: Different applications may have different archival and partitioning requirements. Who makes sure that everyone's needs are met equally?
13. Standardization: If different teams are allowed to manage the common database objects on their own, how are things such as data dictionary, naming conventions, etc. managed? A table that is used by different teams may have differently (and mostly annoyingly) named columns, constraints, etc.

A better approach is to expose the common database tables using a service. The service can keep things consistent and controlled at all times. A common team handling changes to the service can ensure that unexpected changes are minimized and also communicated to all affected parties in a timely manner.