Guidance on Read/Write DataSource Routing in SmartGWT with Multiple JNDI DataSources

[priyanka.barik]

Hi Team,

We have a requirement to support read-write database splitting for performance optimization.

Current Setup

At the Tomcat level, we have configured two JNDI DataSources in context.xml:

jdbc.<DB> → Primary database (read/write)
jdbc.readonly.<DB> → Read-only replica database

Requirement
We want to achieve the following behavior:

All read operations (fetch/search) should use the read-only DataSource
All write operations (add/update/remove) should use the primary DataSource
Questions

We would like guidance on the recommended approach within SmartGWT:

Is it possible to configure this routing directly at the DataSource (.ds.xml) level (e.g., per operationBinding)?
Can DSRequest / server-side request handling be used to dynamically switch the DataSource?
Or is the recommended approach to handle this entirely in the server-side DMI layer by explicitly choosing the appropriate JNDI DataSource?


Are there any SmartGWT-supported patterns or best practices for this use case?

Looking forward to your guidance.

[Isomorphic]

It's important to get the terms straight and you're using DataSource two ways (SmartGWT DataSource and JNDI DataSource). We will use DataSource just for SmartGWT DataSource and we'll use "dbName" for the other. Specifically, you use DataSource.dbName to refer to a set of database settings declared server.properties (could be JNDI or not).

With that out of the way, there are a few conceptual issues with how you're approaching this:

1. cache sync and re-select

Based on the cacheSyncStrategy setting and the presence of server-generated fields (like sequences), the SmartGWT Server will sometimes do an automatic SELECT right after an UPDATE or ADD, to see the data-as-stored, so it can be returned to the browser. You would not want this kind of operation to go to your (stale) read cache

2. transactions

Similar to the above, switching DBs in the middle of a transaction would basically void the purpose of the transaction.

--

Based on the above, you probably at most want to use the read-only DB for pure-read transactions (all fetches, nothing else) and you would also possibly want to audit your "fetch" operations to make sure none of them also do a write of some kind.

But there's more to think about: say a user is working with large dataset in a ListGrid and saves a change, then changes filter criteria in a way that requires a new server fetch, but where the changed record is still going to be in the new data. Presumably the read-only DB is slightly behind (replication), so it will appear that the change was lost.

To avoid this, you can keep track of recent changes, and either:

1) overlay the changes on top of results coming back from the read-only server

Tricky, but we internally have a correct implementation of this, for the DataSource.queueChanges feature in 15.0

2) just pass a flag on all requests saying that there was a recent update in that browser, so don't use the readOnly dbName for that request

This can be either crude or more granular, like pairs of DataSource IDs and timestamps

Either way, the way you actually implement the switching of dbName is to use a DynamicDSGenerator - set a flag on the request if it should use the readOnly dbName and have the generator detect that flag and return the dbName based on that.

However, that crude approach means you have to turn off pooling (read this for background: https://smartclient.com/smartclient-...Implementation) so the better approach is probably to change the DataSource IDs in the DSRequests (e.g. add "_RO" for readOnly), probably in a subclass of IDACall, and then change them back in the DSResponse at the end of the transaction. This allows you to keep pooling on.

Crucially, you don't want the browser to be aware of the special DataSource IDs, or it will mess up cache sync: a cache is associated with the DataSource ID, so it would ignore cache updates if you exposed the "_RO" suffix to the browser. That's why you change the DS ID back once you have the DSResponse.

Finally, if this all sounds complex, you might want to look at the pattern of expensive requests and consider instead client-side caching approaches (like dataSource.cacheAllData:true) or, better yet, first do an overall performance analysis - SmartGWT lightens database load about as much as it's possible to do so (as much as 10x better than any other UI approach), so it's weird that you would even need to look into a readOnly split, and you might have some other, simpler problem.