Finance | Project Operations, Human Resources, ...

Quick question about caching tables

(0) Share

Report

Posted on by DELDYN

550

Hi,

Quick question please about cache lookup:

In some cache lookup types, it's mentioned that inside transactions, the data is returned from SQL instead of cache.

My question now, AFTER ttscommit, if i select the same record that was in transaction before, will this select get the value from cache or do sql hit?

I want to understand if inside the transaction, does the cache entry gets deleted. So that if I select same record after ttscommit, does it read from DB? Or inside the transaction it replaces the cache entry, so that after ttscommit, if i select same record normally it reads from cache directly?

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Subra 1,467 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi

Once the transaction is commited, cache will be updated with the new values and further selection will use only the cache records.

Thanks,
Subra

Was this reply helpful? Yes No
Suggested answer

Subra 1,467 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @DELDYN

Additionally, we have discussed the similar topics in the below forum as well.

https://community.dynamics.com/forums/thread/details/?threadid=38f9cdb5-0f71-f111-ab0d-0022482aa3a2

Thanks,
Subra

If this helped, please mark it as "Verified" for others facing the same issue

Was this reply helpful? Yes No
DELDYN 550 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Subra,

1) Thanks, but is there an official documentation that would verify your below statement?
"Once the transaction is commited, cache will be updated with the new values and further selection will use only the cache records."

Also, in the forum you shared, there is this article shared
https://axm365.com/understanding-caching-and-cache-lookup-property-of-table/

In this article

2) So first select in transaction will always do DB hit
And any "select for update" inside transaction will always do DB hit
But the cache is invalidated not when we call custTable.update() and not when we call ttscommit. The cache is invalidated directly when we do first select inside transaction?

3) So in case of NotInTTs:

select custTable where custTable.AccountNum == '4000'; //if first select then DB hit, if already found in cache, then reads from cache
ttsbegin;

select custTable where custTable.AccountNum == '4000'; //DB hit and cache is invalidated (cache updated)
select custTable where custTable.AccountNum == '4000'; //reads latest value from cache
select forupdate custTable where custTable.AccountNum == '4000'; //DB hit and cache is invalidated (cache updated)
custTable.custGroup = "10";

custTable.update(); //DB hit and referesh cache directly?
select custTable where custTable.AccountNum == '4000'; //reads latest value from cache
select forupdate custTable where custTable.AccountNum == '4000'; //DB hit and cache is invalidated (cache updated)
custTable.custGroup = "30";
custTable.update(); DB hit and referesh cache directly?

ttscommit; //doesn't affect cache?

select custTable where custTable.AccountNum == '4000'; //reads latest value from cache

4) And in case of Found:

select custTable where custTable.AccountNum == '4000' //if first select then DB hit, if already found in cache, then reads from cache
ttsbegin;

select custTable where custTable.AccountNum == '4000'; //reads from cache (no DB hit, different to NotInTTs and it seems like it's the only difference?)
select custTable where custTable.AccountNum == '4000'; //reads from cache
select forupdate custTable where custTable.AccountNum == '4000'; //DB hit and cache is invalidated (cache updated)
custTable.custGroup = "10";

custTable.update(); //DB hit and referesh cache directly?
select custTable where custTable.AccountNum == '4000'; //reads latest value from cache

select forupdate custTable where custTable.AccountNum == '4000'; //DB hit and cache is invalidated (cache updated)

custTable.custGroup = "30";
custTable.update(); //DB hit and referesh cache directly?

ttscommit; //doesn't affect cache?

select custTable where custTable.AccountNum == '4000'; //reads latest value from cache

I would appreciate if you can verify each comment i wrote next to each line

Was this reply helpful? Yes No
Suggested answer

Deepak Agarwal 9,095 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello,

Sort answer is, After ttsCommit, the next select will typically read from cache (not SQL); provided the cache entry is still valid.
The cache entry is NOT deleted just because you entered a transaction. Instead, its behavior depends on: CacheLookup type and Whether the record was updated/inserted/deleted in that transaction

Was this reply helpful? Yes No
DELDYN 550 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Deepak,

Thank you, So if i will re-write my last reply above, can you please verify the below

So in case of NotInTTs:

select custTable where custTable.AccountNum == '4000'; //if first select then DB hit, if already found in cache, then reads from cache
ttsbegin;

select custTable where custTable.AccountNum == '4000'; //DB hit, creates it's own cache (outside cache still not invalidated)
select custTable where custTable.AccountNum == '4000'; //reads latest value from new cache
select forupdate custTable where custTable.AccountNum == '4000'; //DB hit and refreshes new cache, i mean new cache is invalidated (cache before tts is still there)
custTable.custGroup = "10";

custTable.update(); //DB hit and refereshes new cache?
select custTable where custTable.AccountNum == '4000'; //reads latest value from new cache
select forupdate custTable where custTable.AccountNum == '4000'; //DB hit and refreshes new cache
custTable.custGroup = "30";
custTable.update(); //DB hit and refereshes new cache?

ttscommit; //maybe here old cache gets updated with the new cache?

select custTable where custTable.AccountNum == '4000'; //reads latest value from cache

I would appreciate if you can confirm my comments in blue

And the 2nd point to confirm please, is that the only difference between NotInTTs and Found, is that in Found, the first normal select in transaction doesn't do a DB hit but rather reads from cache (doesn't create a new cache), it only creates it's own cache if we do select for update() ?

Was this reply helpful? Yes No