Member Avatar for warlord902

I want to know, is it possible to use a mySQL database simultaneously from a PHP website and a Java Website both making continous read and write operations. Are there any tradeoffs?

Does connection pooling affect this?

  • 2 Contributors
  • 1 Reply
  • 206 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Member #682468

Dani AI

Generated

Short answer to : yes — a single MySQL instance can be shared by a PHP site and a Java site doing continuous reads and writes. The real questions are not “can” but “how to avoid contention, keep transactions short, and scale connections.” rightly flagged storage engines; expanding on that will help pick the right approach.

Prefer a transactional, row-locking engine (InnoDB) for mixed read/write workloads because most reads use MVCC and won’t block concurrent writes. That said, heavy write hotspots, long transactions, or poorly indexed queries will still create lock waits and deadlocks. Connection pooling (Java) and cautious use of persistent connections (PHP) reduce connection/handshake overhead and smooth spikes, but they do not eliminate database-level contention — pool sizes must be tuned so total client connections don’t overwhelm the server.

Practical patterns and quick remedies:

  • Keep transactions as short as possible; don’t wrap user interaction in a DB transaction.

  • Add proper indexes to avoid long table scans.

  • Use optimistic locking (version column) for frequent concurrent updates. Example:

    UPDATE orders
SET status = 'paid', version = version + 1
WHERE id = ? AND version = ?;

    If zero rows are affected, retry or handle the conflict.

  • Offload read-heavy traffic to replicas, but accept possible replication lag (read-your-writes must hit primary).

  • Monitor: slow query log, connection counts, InnoDB lock waits and deadlocks (SHOW PROCESSLIST, SHOW ENGINE INNODB STATUS) and tune accordingly.

For high throughput, consider batching writes, queueing writes through a message broker, partitioning hot tables, or using read replicas. Test under realistic load and iterate on indexes, pool sizes, and transaction patterns before assuming production behavior.

Member Avatar for Member #682468

From my understanding MySQL locks a unit upon writing to ensure no reads happen during that: so writing blocks any reading you do. Depending on the storage engine you use (more on that here) it may only block a row; or the entire table; you should probably choose to do rows, although it takes more resources to do that, as your DB will be accessed by multiple applications that may not hold their own caches, etc. and blocking one sounds like it would be disasterous.

A connection pool should reduce the amount of stress on your DB making connections and the amount of time it takes for the Java or PHP to fetch data, it should help, but not during the query aspect.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.