Why the memory increases if I don't do INSERTS inside a Transaction?

[Hernán J. González]

I'm stuck with some weird (it seems) memory leak. I'm using MyBatis 3.4.5 with Spring Boot 1.58, Java 8 and SQL Server 2014.
I've reduced my case to the following:I'm reading in a loop, a single large table (say CLIENTE ~ 900K rows). I read in paged mode (20K) using a xml mapper (details below).For each record, I sometimes insert a record in a second table (say TESTIGO) (mapper below).There's practically no relation between the records read from CLIENTE and the inserted in TESTIGO.
All quite simple, and it works ok. Except for this:
If a run the full loop inside a Transaction, then it works ok until I stop saving - at that momentthen the memory consumption starts growing quickly. (no, I'm not storing anything in memory) I all goes to normality when I save some record, but if I don't, I eventually reach an OutOfMemory exception.I've tried with and without the spring settingmybatis.executor-type=REUSEwith no difference.
It's like MyBatis (or Spring?) does not like that I keep doing SELECTs inside an open transactionif I don't do now and them some UPDATE/INSERT ? Why?
For example, here I (artificially) decided to not save anything for pages 5 to 11
I copy some result below. See how memory increases after page 5
From some heap dumps I'm seeing, it seems that some org.apache.ibatis.cache.impl.PerpetualCache is eating the memory. I can provide more details if needed.
Regards   Hernan
======================================
Output:

with transaction, No REUSEpage [pag=0, n=20000] mem=41MB saving=true secs=7 tx=truepage [pag=1, n=20000] mem=41MB saving=true secs=13 tx=truepage [pag=2, n=20000] mem=41MB saving=true secs=19 tx=truepage [pag=3, n=20000] mem=41MB saving=true secs=25 tx=truepage [pag=4, n=20000] mem=41MB saving=false secs=28 tx=truepage [pag=5, n=20000] mem=74MB saving=false secs=30 tx=truepage [pag=6, n=20000] mem=107MB saving=false secs=32 tx=truepage [pag=7, n=20000] mem=140MB saving=false secs=35 tx=truepage [pag=8, n=20000] mem=173MB saving=false secs=38 tx=truepage [pag=9, n=20000] mem=206MB saving=false secs=40 tx=truepage [pag=10, n=20000] mem=239MB saving=false secs=45 tx=truepage [pag=11, n=20000] mem=272MB saving=false secs=48 tx=truepage [pag=12, n=20000] mem=41MB saving=true secs=54 tx=true

within transactions -  REUSEpage [pag=0, n=20000] mem=41MB saving=true secs=5 tx=truepage [pag=1, n=20000] mem=41MB saving=true secs=10 tx=truepage [pag=2, n=20000] mem=41MB saving=true secs=14 tx=truepage [pag=3, n=20000] mem=41MB saving=true secs=18 tx=truepage [pag=4, n=20000] mem=41MB saving=false secs=21 tx=truepage [pag=5, n=20000] mem=74MB saving=false secs=23 tx=truepage [pag=6, n=20000] mem=107MB saving=false secs=25 tx=truepage [pag=7, n=20000] mem=140MB saving=false secs=28 tx=truepage [pag=8, n=20000] mem=173MB saving=false secs=31 tx=truepage [pag=9, n=20000] mem=206MB saving=false secs=33 tx=truepage [pag=10, n=20000] mem=239MB saving=false secs=38 tx=truepage [pag=11, n=20000] mem=272MB saving=false secs=41 tx=truepage [pag=12, n=20000] mem=41MB saving=true secs=45 tx=true
NO  transaction -  REUSEpage [pag=0, n=20000] mem=41MB saving=true secs=10 tx=falsepage [pag=1, n=20000] mem=41MB saving=true secs=18 tx=falsepage [pag=2, n=20000] mem=41MB saving=true secs=27 tx=falsepage [pag=3, n=20000] mem=41MB saving=true secs=35 tx=falsepage [pag=4, n=20000] mem=41MB saving=false secs=37 tx=falsepage [pag=5, n=20000] mem=41MB saving=false secs=40 tx=falsepage [pag=6, n=20000] mem=41MB saving=false secs=42 tx=falsepage [pag=7, n=20000] mem=41MB saving=false secs=45 tx=falsepage [pag=8, n=20000] mem=41MB saving=false secs=47 tx=falsepage [pag=9, n=20000] mem=41MB saving=false secs=49 tx=falsepage [pag=10, n=20000] mem=41MB saving=false secs=52 tx=falsepage [pag=11, n=20000] mem=41MB saving=false secs=54 tx=falsepage [pag=12, n=20000] mem=41MB saving=true secs=63 tx=false
Mappers
<select id="selectByPage" parameterType="myapp.db.Page" resultMap="BaseResultMap"><![CDATA[   SELECT * FROM   ( SELECT ROW_NUMBER() OVER ( ORDER BY TipoDocumento,NumeroDocumento )   AS RowNum, * FROM CLIENTES) AS RowConstrainedResult   WHERE   RowNum >=  #{rowNumFrom,jdbcType=INTEGER}   AND RowNum <= #{rowNumTo,jdbcType=INTEGER}  ORDER BY RowNum   ]]></select>
<update id="truncate" parameterType="java.lang.String">    TRUNCATE TABLE TESTIGO</update><insert id="insert" parameterType="myapp.db.Testigo">    insert into TESTIGO (DOC)   values (#{doc,jdbcType=VARCHAR})</insert>  ------------Java code:
public void rebuildTestigos(boolean inTransaction) {    final int PAGESIZE = 20000; Page page = new Page(PAGESIZE); long t0 = System.currentTimeMillis(); TransactionStatus tx = inTransaction ? dbservice.startTransaction() : null; try { dbservice.getTestigoMapper().truncate(""); do { boolean save = page.getPageNum() < 4 || page.getPageNum() > 11; List<WhClientes> clis = dbservice.getWhClientsMapper().selectByPage(page); for( WhClientes c : clis )  if(save) saveRecord(c.docAsString()); LOG.info("page {} mem={}MB saving={} secs={} tx={}", page, getUsedMemory(true) / (1024 * 1024),                 save, (System.currentTimeMillis() - t0) / 1000, inTransaction); if( clis.size() < PAGESIZE ) break; page = page.nextPage(); } while(true); } catch(Exception e) { if( inTransaction ) dbservice.rollback(tx); e.printStackTrace(); } if( inTransaction ) dbservice.commit(tx); } private void saveRecord( String id) { Testigo t = new Testigo(); t.setDoc(id); dbservice.getTestigoMapper().insert(t); } /** quick global estimate */ public static long getUsedMemory(boolean gc) { if( gc ) System.gc(); return Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory(); }

[Iwao AVE!]

Hi Hernán,

It happens if you enabled second level caching.

Let's consider the following scenario.

1. open session

2. insert a row into table_a

3. select the inserted row from table_a

4. rollback

5. close session

At step 3, if the query result is written into the second level cache directly, the cache will contain a row that does not exist in the database.

To avoid it, MyBatis keeps the query result on memory (it's called transactional cache) and writes it into the second level cache at the end of the session if it's still valid.

And if you execute INSERT, UPDATE or DELETE in the same session, the transactional cache is immediately cleared (because the content cannot be trusted anymore) and the memory is released.

There may be several workarounds.

- Not to use transaction (you've found this already).

- Not to use second level cache on this mapper.

- Add flushCache="true" to selectByPage statement.

Hope this helps,

Iwao

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Hernán J. González]

That makes sense now. I didn't expect the 2nd level cache to be enabled by default, 

I've disabled it (and also set localCacheScope to STATEMENT) and all lloks ok now. 

Thanks!

Hernan

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/dbYMxhciQhU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mybatis-user+unsubscribe@googlegroups.com.

[Iwao AVE!]

2nd level cache is disabled by default, so it might be the local cache that consumed the memory in your case.

If so, it was `localCacheScope="STATEMENT"` that resolved your problem.

// Iwao

[Hernán J. González]

> 2nd level cache is disabled by default

Are you sure? I guessed that the global setting "cacheEnabled" refers to the 2nd level cache 

(the local cache cannot be disabled). And by default it's true

http://www.mybatis.org/mybatis-3/configuration.html#settings

(and I've checked that by asking Configuration object of my session object - it only 

returned false when I explicitly set cacheEnabled=false)

It would be nice if the docs make it clear if this referers to the 2nd level cache, it only says 

"Globally enables or disables any caches configured in any mapper under this configuration."

Confusingly, this page refers to a <cache> setting, which I couldn't find

http://moi.vonos.net/java/mybatis-caching/

 

Hernán J. González

Buenos Aires, Argentina

hjg.c...@gmail.com

[Iwao AVE!]

Sorry about the confusion.

cacheEnabled is a global switch for the 2nd level cache and its default value is true.

But 2nd level cache must be enabled for each mapper by adding <cache /> element or @CacheNamespace annotation.

It's explained in the following section.

http://www.mybatis.org/mybatis-3/sqlmap-xml.html#cache

// Iwao

[Hernán J. González]

Ah, it indeed makes sense now (it felt weird that I had a second level cache set by default!).

Thanks again.

[Erwan Letessier]

Hi Hernán,

I know that you present here a reduced case, but I don't know how far it is from reality.

So no offence intended and  here are few things you could consider:

- the save test cold be done out of the for loop, because here, for pages 5-11, the client list is iterated on for free.

- every page of client is stored in in-memory list that is is then iterated on for subsequent save; passing a custom ResultHander to the select would allow to processing save directly when fetching every row from ResultSet, so that you save the memory  to store the list and an iteration over this list.

- transaction management should be delegated to Spring.

Cheers

Erwan

Erwan Letessier

[Hernán J. González]

On Mon, Nov 27, 2017 at 10:44 AM, Erwan Letessier <erwan.l...@gmail.com> wrote:

Hi Hernán,

I know that you present here a reduced case, but I don't know how far it is from reality.

So no offence intended and  here are few things you could consider:

- the save test cold be done out of the for loop, because here, for pages 5-11, the client list is iterated on for free.

Yes, that's a thing of the reduced case, in real life I don't know when (if ever) I stop saving records.

 

- every page of client is stored in in-memory list that is is then iterated on for subsequent save; passing a custom ResultHander to the select would allow to processing save directly when fetching every row from ResultSet, so that you save the memory  to store the list and an iteration over this list.

That would be a good optimization, I agree.

 

- transaction management should be delegated to Spring.

It actually is, it's just using the programatic way 

https://docs.spring.io/spring/docs/3.1.x/spring-framework-reference/html/transaction.html#transaction-programmatic

(sec 11.6.2 )

Thanks for the suggestions 

  Hernan

 

Cheers

Erwan

Erwan Letessier