How to Use Transactions in SAP Commerce Cloud (Hybris) to Reduce Database Load

Disclaimer: the version of SAP Commerce described in this article is dated 2019, so be sure to check the information on current versions.

While high e-commerce platform performance is sometimes achieved with the blood, sweat, and tears of dev teams, it’s surprising how often basic Hybris performance techniques for improvements are overlooked. Take transactions, for example.

During their work, Expert Soft developers have participated in several projects that are based on the SAP Commerce Cloud (Hybris) e-commerce platform. And there is one thing that they found in common for many projects — transactions are used very occasionally.

In most cases, it is the out-of-the-box code and, more rarely, it’s code where transactions are apparently necessary and unavoidable, e.g. stock update. The vast majority of the project code doesn’t use transactions at all.

It could seem to be a disaster because none of the A.C.I.D. principles are guaranteed. Surprisingly, these projects are in “live” and are used more or less successfully concerning business needs.

Well, this may happen because data that is modified actively belong to one of these groups:

• data that does not suffer from non-atomic updates (e.g. product data) or is being rewritten regularly from external feeds so that errors do not accumulate;
• data that is attached to a single user session naturally (cart, customer profile, order), and the user does not force data races in regular use.

While almost nobody uses transactions for data integrity in SAP Commerce (Hybris) and applications still work fine in most cases, why should we care about transactions? There are at least two reasons for this.

First, there is a huge difference between code that doesn’t corrupt data in most cases and code that doesn’t corrupt data in all possible cases. It should be clear for developers who spent hours or days troubleshooting a “mysterious problem”.

Second, transactions greatly improve the performance of the Hybris code. Let’s look into this aspect in more detail.

Many SAP Commerce developers think that transactions are slow and error-prone because tables and/or table rows can be locked within a transaction, causing an overhead on keeping the transaction state. And, the worse, many developers believe so just because other developers tell them that transactions are slow. What do we have in reality?

These myths are easily dispelled by the fact that when there is no explicit transaction started by the developer, each persistence operation is conducted in its transaction (i.e. in the auto-commit mode). So, the runtime is full of transactions!

Let’s consider a simple example when a new item is added to the shopping cart.

final CartModificationData cartModification = cartFacade.addToCart(code, qty);

This line is simple and self-explained. The out-of-the-fox cart facade is used here and is run by the out-of-the-box AddToCartController outside a transaction. Let’s look at what calls to the DB this line produces:

1. 1. SELECT PK FROM cartentries item_t0 WHERE (( item_t0.p_entrynumber = 0 AND
item_t0.p_order = 8796093087787 )) AND
(item_t0.TypePkString=8796098363474 )

1. 2. SELECT PK FROM cartentries item_t0 WHERE ( item_t0.p_order =
8796093087787) AND (item_t0.TypePkString=8796098363474 ) order by
item_t0.p_entrynumber ASC

1. 3. INSERT INTO cartentries
( hjmpTS,PK,createdTS,modifiedTS,TypePkString,
p_baseprice,p_calculated,p_entrynumber,
p_giveaway,p_info,p_order,p_product,
p_quantity,p_rejected,p_taxvaluesinternal,
p_totalprice,p_unit )
VALUES (0,8796093120556,'2019-04-15 17:26:34.583','2019-04-15 17:26:34.583', 8796098363474,0.0,0,0,0,'product "816802" with name "Cyber-shot W55"', 8796093087787,8796231958529,1,0,'[]',
0.0,8796093054986)

1. 4. SELECT * FROM cartentries WHERE PK='8796093120556'

1. 5. SELECT * FROM props WHERE ITEMPK='8796093120556'

1. 6. INSERT INTO cartentries44sn (itempk, itemtypepk, timestamp, currenttimestamp,
changinguser, payloadbefore, payloadafter, operationtype, context)
VALUES ('8796093120556', '8796098363474', '2019-04-15 17:26:34.583',
'2019-04-15 17:26:34.587', 'anonymous', '{"mapBasedAttributes":{},
"attributes":{},"locAttributes":{}}', '{"mapBasedAttributes":{},
"attributes":{"product":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796231958529"]},"quantity":{"type":{"type":"java.lang.Long",
"collection":""},
"value":["1"]},"rejected":{"type":{"type":"java.lang.Boolean",
"collection":""},
"value":["false"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},
"value":["0.0"]},"entrynumber":{"type":{"type":"java.lang.Integer",
"collection":""},
"value":["0"]},"taxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},
"value":["[]"]},"baseprice":{"type":{"type":"java.lang.Double",
"collection":""},
"value":["0.0"]},"unit":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093054986"]},"modifiedtime":{"type":{"type":"java.util.Date","collection":""},
"value":["1555338394583"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796098363474"]},"creationtime":{"type":{"type":"java.util.Date","collection":""},
"value":["1555338394583"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120556"]},"calculated":{"type":{"type":"java.lang.Boolean","collection":""},
"value":["false"]},"giveaway":{"type":{"type":"java.lang.Boolean","collection":""},
"value":["false"]},"info":{"type":{"type":"java.lang.String","collection":""},
"value":["product \"816802\" with name \"Cyber-shot W55\""]},"order":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]}},"locAttributes":{}}', '1', '{}')


1. 7. commit

1. 8. SELECT * FROM cartentries WHERE PK=8796093120556

1. 9. SELECT * FROM composedtypes WHERE jaloClassName = 'de.hybris.platform.jalo.product.Unit'

1. 10. SELECT PK FROM units item_t0 WHERE ( item_t0.p_unittype ='pieces'
AND item_t0.PK <>8796093054986) AND (item_t0.TypePkString=8796098003026 )

1. 11. SELECT x.col_PK FROM (SELECT item_t0.PK AS col_PK FROM discountrows item_t0 WHERE
( item_t0.p_productmatchqualifier in (0, 8796231958529) and item_t0.p_usermatchqualifier in (0, 8796093087748))
AND (item_t0.TypePkString=8796111142994 AND (( item_t0.p_product IS NOT NULL OR item_t0.p_catalogversion
IS NULL OR item_t0.p_catalogversion IN (8796093350489,8796093383257,8796093448793) )))
UNION SELECT item_t1.PK AS col_PK FROM discountrows item_t1 WHERE ( item_t1.p_productmatchqualifier =-1 and
item_t1.p_productid ='816802' and item_t1.p_usermatchqualifier in (0, 8796093087748))
AND (item_t1.TypePkString=8796111142994 AND (( item_t1.p_product IS NOT NULL OR item_t1.p_catalogversion IS NULL OR
item_t1.p_catalogversion IN (8796093350489,8796093383257,8796093448793) )))) x

1. 12. SELECT * FROM cartentries WHERE PK='8796093120556'

1. 13. SELECT * FROM props WHERE ITEMPK='8796093120556'

1. 14. UPDATE cartentries SET hjmpTS = 1 ,modifiedTS='2019-04-15 17:26:34.631',p_baseprice=260.87,
p_calculated=1,p_discountvaluesinternal='[]',
p_taxvaluesinternal='[<TV<jp-vat-full#5.0#false#12.42#USD>VT>]',
p_totalprice=260.87
WHERE PK = 8796093120556 AND (sealed IS NULL OR sealed=0)

1. 15. SELECT * FROM cartentries WHERE PK='8796093120556'

1. 16. SELECT * FROM props WHERE ITEMPK='8796093120556'

1. 17. INSERT INTO cartentries44sn (itempk, itemtypepk, timestamp, currenttimestamp, changinguser, payloadbefore, payloadafter, operationtype, context)
VALUES ('8796093120556', '8796098363474', '2019-04-15 17:26:34.631', '2019-04-15 17:26:34.633', 'anonymous', '{"mapBasedAttributes":{},"attributes":{"product":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796231958529"]},"quantity":{"type":{"type":"java.lang.Long","collection":""},
"value":["1"]},"rejected":{"type":{"type":"java.lang.Boolean","collection":""},
"value":["false"]},"totalprice":{"type":{"type":"java.lang.Double","collection":""},
"value":["0.0"]},"entrynumber":{"type":{"type":"java.lang.Integer","collection":""},
"value":["0"]},"taxvaluesinternal":{"type":{"type":"java.lang.String","collection":""},
"value":["[]"]},"baseprice":{"type":{"type":"java.lang.Double","collection":""},
"value":["0.0"]},"unit":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},
"value":["8796093054986"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},
"value":["1555338394583"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},
"value":["8796098363474"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},
"value":["1555338394583"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},
"value":["8796093120556"]},"calculated":{"type":{"type":"java.lang.Boolean","collection":""},
"value":["false"]},"giveaway":{"type":{"type":"java.lang.Boolean","collection":""},
"value":["false"]},"info":{"type":{"type":"java.lang.String","collection":""},
"value":["product \"816802\" with name \"Cyber-shot W55\""]},
"order":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},
"value":["8796093087787"]}},"locAttributes":{}}', '{"mapBasedAttributes":{},"attributes":{"product":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796231958529"]},"quantity":{"type":{"type":"java.lang.Long",
"collection":""},"value":["1"]},"rejected":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"entrynumber":{"type":{"type":"java.lang.Integer",
"collection":""},"value":["0"]},"taxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#12.42#USD>VT>]"]},
"baseprice":{"type":{"type":"java.lang.Double","collection":""},
"value":["260.87"]},"unit":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093054986"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394631"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796098363474"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394583"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120556"]},"discountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[]"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["true"]},"giveaway":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"info":{"type":{"type":"java.lang.String",
"collection":""},"value":["product \"816802\" with name \"Cyber-shot W55\""]},"order":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]}},"locAttributes":{}}', '2', '{}')

1. 18. commit

1. 19. SELECT * FROM cartentries WHERE PK=8796093120556

1. 20. commit

1. 21. SELECT PK FROM cartentries item_t0 WHERE ( item_t0.p_order =8796093087787)
AND (item_t0.TypePkString=8796098363474 ) order by item_t0.p_entrynumber ASC, item_t0.createdTS ASC

1. 22. SELECT * FROM carts WHERE PK='8796093087787'

1. 23. SELECT * FROM props WHERE ITEMPK='8796093087787'

1. 24. UPDATE carts SET hjmpTS = 7 ,modifiedTS='2019-04-15 17:26:34.639',p_subtotal=260.87,
p_totalprice=260.87,p_totaltax=12.42,
p_totaltaxvaluesinternal='[<TV<jp-vat-full#5.0#false#12.42#NULL>VT>]'
WHERE PK = 8796093087787 AND (sealed IS NULL OR sealed=0)

1. 25. SELECT * FROM carts WHERE PK='8796093087787'

1. 26. SELECT * FROM props WHERE ITEMPK='8796093087787'

1. 27. INSERT INTO carts43sn (itempk, itemtypepk, timestamp, currenttimestamp, changinguser, payloadbefore, payloadafter, operationtype, context)
VALUES ('8796093087787', '8796094038098', '2019-04-15 17:26:34.639', '2019-04-15 17:26:34.64', 'anonymous', '{"mapBasedAttributes":{},"attributes":{"date":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"code":{"type":{"type":"java.lang.String",
"collection":""},"value":["00000001"]},"globaldiscountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[]"]},"paymentcost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"totaltaxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#11.47#NULL>VT>]"]},
"discountsincludepaymentcost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"sessionid":{"type":{"type":"java.lang.String",
"collection":""},"value":["D366641C62BC856D467A4C07EF47C0D3"]},
"store":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093122525"]},"deliverycost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"discountsincludedeliverycost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totaltax":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"site":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093121576"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338365115"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796094038098"]},"subtotal":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"guid":{"type":{"type":"java.lang.String",
"collection":""},"value":["83e2f191-2720-4e10-a4e6-9ae1e83bf91a"]},"totaldiscounts":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"currency":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120545"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]},"net":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["true"]},"user":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087748"]}},"locAttributes":{}}', '{"mapBasedAttributes":{},"attributes":{"date":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"code":{"type":{"type":"java.lang.String",
"collection":""},"value":["00000001"]},"globaldiscountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[]"]},"paymentcost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"totaltaxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#12.42#NULL>VT>]"]},
"discountsincludepaymentcost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"sessionid":{"type":{"type":"java.lang.String",
"collection":""},"value":["D366641C62BC856D467A4C07EF47C0D3"]},
"store":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093122525"]},"deliverycost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"discountsincludedeliverycost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totaltax":{"type":{"type":"java.lang.Double",
"collection":""},"value":["12.42"]},"site":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093121576"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394639"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796094038098"]},"subtotal":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"guid":{"type":{"type":"java.lang.String",
"collection":""},"value":["83e2f191-2720-4e10-a4e6-9ae1e83bf91a"]},"totaldiscounts":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"currency":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120545"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]},"net":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["true"]},"user":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087748"]}},"locAttributes":{}}', '2', '{}')

1. 28. commit

1. 29. SELECT * FROM carts WHERE PK=8796093087787

1. 30. SELECT PK FROM cartentries item_t0 WHERE ( item_t0.p_order =8796093087787) AND
(item_t0.TypePkString=8796098363474 ) order by item_t0.p_entrynumber ASC,
item_t0.createdTS ASC

1. 31. commit

1. 32. commit

1. 33. commit

1. 34. SELECT PK FROM catver2rulescontext item_t0 WHERE ( item_t0.p_catalogversion
IN (8796093350489))
AND (item_t0.TypePkString=8796115992658 )

1. 35. SELECT PK FROM campaign item_t0 WHERE ( item_t0.p_enabled = 1 AND ( item_t0.p_startdate IS NULL OR
item_t0.p_startdate <= '2019-04-15 17:26:34.649') AND ( item_t0.p_enddate IS NULL OR
item_t0.p_enddate >= '2019-04-15 17:26:34.649')) AND (item_t0.TypePkString=8796118188114 )

1. 36. SELECT * FROM attributedescriptors WHERE EnclosingTypePK=8796093382738

1. 37. SELECT PK FROM carts item_t0 WHERE ( item_t0.p_code ='00000001') AND (item_t0.TypePkString=8796094038098 )
UNION ALL SELECT item_t0.PK FROM orders item_t0 WHERE ( item_t0.p_code ='00000001')
AND (item_t0.TypePkString IN (8796097609810,8796097675346,8796094169170) )
UNION ALL SELECT item_t0.PK FROM quotes item_t0 WHERE ( item_t0.p_code ='00000001') AND (item_t0.TypePkString=8796094103634 )

1. 38. SELECT PK FROM enginerules item_t0 JOIN droolskiebase item_t1 ON item_t0.p_kiebase = item_t1.PK JOIN rulesmodule item_t2
ON item_t1.p_kiemodule = item_t2.PK WHERE ( item_t2.p_name = 'promotions-module' AND item_t0.p_code = 'order_threshold_fixed_discount')
AND ((item_t0.TypePkString=8796115566674 AND item_t1.TypePkString=8796115599442
AND item_t2.TypePkString=8796115697746 ))

1. 39. SELECT * FROM attributedescriptors WHERE EnclosingTypePK=8796115566674

1. 40. INSERT INTO promotionresult ( hjmpTS,PK,createdTS,modifiedTS,TypePkString,
p_certainty,p_order,p_promotion )
VALUES (0,8796093191061,'2019-04-15 17:26:34.67','2019-04-15 17:26:34.67',8796113731666,'1.0',
8796093087787,8796094108562)

1. 41. SELECT PK FROM orderentryconsumed item_t0 WHERE ( item_t0.p_promotionresult = 8796093191061)
AND (item_t0.TypePkString=8796114485330 )

1. 42. SELECT * FROM promotionresultlp WHERE ITEMPK=8796093191061 AND LANGPK=8796093055008

1. 43. INSERT INTO promotionresultlp ( p_messagefired,ITEMPK,ITEMTYPEPK,LANGPK )
VALUES ( 'Buy over $200.00 get $20.00 discount on cart',8796093191061,
8796113731666,8796093055008 )

1. 44. UPDATE promotionresult SET hjmpTS = 1 ,modifiedTS='2019-04-15 17:26:34.672',p_moduleversion=23,
p_ordercode='00000001',p_ruleversion=22,
p_rulesmodulename='promotions-module'
WHERE PK = 8796093191061 AND (sealed IS NULL OR sealed=0)

1. 45. commit

1. 46. SELECT * FROM promotionresult WHERE PK=8796093191061

1. 47. INSERT INTO promotionaction ( hjmpTS,PK,createdTS,modifiedTS,
TypePkString,p_amoun0,p_guid,p_markedapplied,
p_promotionresult,p_rule,p_strategyid )
VALUES (0,8796093191063,'2019-04-15 17:26:34.675','2019-04-15 17:26:34.675',8796114288722,'20.00000',
'Action[ef172776-a299-4c09-b98d-071c3c38a6f3]',1,8796093191061,
8796093748204,
'defaultOrderAdjustTotalActionStrategy')

1. 48. commit

1. 49. SELECT * FROM promotionaction WHERE PK=8796093191063

1. 50. SELECT * FROM carts WHERE PK='8796093087787'

1. 51. SELECT * FROM props WHERE ITEMPK='8796093087787'

1. 52. UPDATE carts SET hjmpTS = 8 ,modifiedTS='2019-04-15 17:26:34.679',p_calculated=0,
p_globaldiscountvaluesinternal='
[<DV<Action[ef172776-a299-4c09-b98d-071c3c38a6f3]
#20.0#true#0.0#USD#false>VD>]'
WHERE PK = 8796093087787 AND (sealed IS NULL OR sealed=0)

1. 53. SELECT * FROM carts WHERE PK='8796093087787'

1. 54. SELECT * FROM props WHERE ITEMPK='8796093087787'

1. 55. INSERT INTO carts43sn (itempk, itemtypepk, timestamp, currenttimestamp, changinguser, payloadbefore, payloadafter, operationtype, context)
VALUES ('8796093087787', '8796094038098', '2019-04-15 17:26:34.679', '2019-04-15 17:26:34.68', 'anonymous', '{"mapBasedAttributes":{},"attributes":{"date":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"code":{"type":{"type":"java.lang.String",
"collection":""},"value":["00000001"]},"globaldiscountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[]"]},"paymentcost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"totaltaxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#12.42#NULL>VT>]"]},
"discountsincludepaymentcost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"sessionid":{"type":{"type":"java.lang.String",
"collection":""},"value":["D366641C62BC856D467A4C07EF47C0D3"]},
"store":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093122525"]},"deliverycost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"discountsincludedeliverycost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totaltax":{"type":{"type":"java.lang.Double",
"collection":""},"value":["12.42"]},"site":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093121576"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394639"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796094038098"]},"subtotal":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"guid":{"type":{"type":"java.lang.String",
"collection":""},"value":["83e2f191-2720-4e10-a4e6-9ae1e83bf91a"]},"totaldiscounts":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"currency":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120545"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]},"net":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["true"]},"user":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087748"]}},"locAttributes":{}}', '{"mapBasedAttributes":{},"attributes":{"date":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"code":{"type":{"type":"java.lang.String",
"collection":""},"value":["00000001"]},"globaldiscountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<DV<Action[ef172776-a299-4c09-b98d-071c3c38a6f3]
#20.0#true#0.0#USD#false>VD>]"]},
"paymentcost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"totaltaxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#12.42#NULL>VT>]"]},
"discountsincludepaymentcost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"sessionid":{"type":{"type":"java.lang.String",
"collection":""},"value":["D366641C62BC856D467A4C07EF47C0D3"]},
"store":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093122525"]},"deliverycost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"discountsincludedeliverycost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totaltax":{"type":{"type":"java.lang.Double",
"collection":""},"value":["12.42"]},"site":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093121576"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394679"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796094038098"]},"subtotal":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"guid":{"type":{"type":"java.lang.String",
"collection":""},"value":["83e2f191-2720-4e10-a4e6-9ae1e83bf91a"]},"totaldiscounts":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"currency":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120545"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]},"net":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"user":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087748"]}},"locAttributes":{}}', '2', '{}')

1. 56. commit

1. 57. SELECT * FROM carts WHERE PK=8796093087787

1. 58. commit

1. 59. commit

1. 60. SELECT * FROM carts WHERE PK='8796093087787'

1. 61. SELECT * FROM props WHERE ITEMPK='8796093087787'

1. 62. UPDATE carts SET hjmpTS = 9 ,modifiedTS='2019-04-15 17:26:34.686',p_calculated=1,
p_globaldiscountvaluesinternal='
[<DV<Action[ef172776-a299-4c09-b98d-071c3c38a6f3]
#20.0#true#20.0#USD#false>VD>]',
p_totaldiscounts=20.0,
p_totalprice=240.87,p_totaltax=11.47,
p_totaltaxvaluesinternal=
'[<TV<jp-vat-full#5.0#false#11.47#NULL>VT>]'
WHERE PK = 8796093087787 AND (sealed IS NULL OR sealed=0)

1. 63. SELECT * FROM carts WHERE PK='8796093087787'

1. 64. SELECT * FROM props WHERE ITEMPK='8796093087787'

1. 65. INSERT INTO carts43sn (itempk, itemtypepk, timestamp, currenttimestamp, changinguser, payloadbefore, payloadafter, operationtype, context)
VALUES ('8796093087787', '8796094038098', '2019-04-15 17:26:34.686', '2019-04-15 17:26:34.688', 'anonymous', '{"mapBasedAttributes":{},"attributes":{"date":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"code":{"type":{"type":"java.lang.String",
"collection":""},"value":["00000001"]},"globaldiscountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<DV<Action[ef172776-a299-4c09-b98d-071c3c38a6f3]
#20.0#true#0.0#USD#false>VD>]"]},
"paymentcost":{"type":{"type":"java.lang.Double",
"collection":""},
"value":["0.0"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"totaltaxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#12.42#NULL>VT>]"]},
"discountsincludepaymentcost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"sessionid":{"type":{"type":"java.lang.String",
"collection":""},"value":["D366641C62BC856D467A4C07EF47C0D3"]},
"store":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093122525"]},"deliverycost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"discountsincludedeliverycost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totaltax":{"type":{"type":"java.lang.Double",
"collection":""},"value":["12.42"]},"site":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093121576"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394679"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796094038098"]},"subtotal":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"guid":{"type":{"type":"java.lang.String",
"collection":""},"value":["83e2f191-2720-4e10-a4e6-9ae1e83bf91a"]},"totaldiscounts":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"currency":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120545"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]},"net":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"user":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087748"]}},"locAttributes":{}}', '{"mapBasedAttributes":{},"attributes":{"date":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"code":{"type":{"type":"java.lang.String",
"collection":""},"value":["00000001"]},"globaldiscountvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<DV<Action[ef172776-a299-4c09-b98d-071c3c38a6f3]
#20.0#true#20.0#USD#false>VD>]"]},
"paymentcost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"totalprice":{"type":{"type":"java.lang.Double",
"collection":""},"value":["240.87"]},"totaltaxvaluesinternal":{"type":{"type":"java.lang.String",
"collection":""},"value":["[<TV<jp-vat-full#5.0#false#11.47#NULL>VT>]"]},
"discountsincludepaymentcost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"sessionid":{"type":{"type":"java.lang.String",
"collection":""},"value":["D366641C62BC856D467A4C07EF47C0D3"]},
"store":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093122525"]},"deliverycost":{"type":{"type":"java.lang.Double",
"collection":""},"value":["0.0"]},"discountsincludedeliverycost":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"totaltax":{"type":{"type":"java.lang.Double",
"collection":""},"value":["11.47"]},"site":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093121576"]},"modifiedtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555338394686"]},"itemtype":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796094038098"]},"subtotal":{"type":{"type":"java.lang.Double",
"collection":""},"value":["260.87"]},"guid":{"type":{"type":"java.lang.String",
"collection":""},"value":["83e2f191-2720-4e10-a4e6-9ae1e83bf91a"]},"totaldiscounts":{"type":{"type":"java.lang.Double",
"collection":""},"value":["20.0"]},"currency":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093120545"]},"creationtime":{"type":{"type":"java.util.Date",
"collection":""},"value":["1555337984857"]},"pk":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087787"]},"net":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["false"]},"calculated":{"type":{"type":"java.lang.Boolean",
"collection":""},"value":["true"]},"user":{"type":{"type":"de.hybris.platform.core.PK",
"collection":""},"value":["8796093087748"]}},"locAttributes":{}}', '2', '{}')

1. 66. commit

1. 67. SELECT * FROM carts WHERE PK=8796093087787

1. 68. SELECT PK FROM abstrcfgproductinfo item_t0 WHERE ( item_t0.p_orderentry =8796093120556)
AND (item_t0.TypePkString IN (8796109766738,8796109701202) ) order by item_t0.p_orderentrypos ASC, item_t0.createdTS ASC

1. 69. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093087795)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 70. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093153331)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 71. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093186099)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 72. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093218867)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 73. SELECT PK FROM commentitemrelations item_t0 JOIN comments item_t1 ON item_t0.SourcePK = item_t1.PK
WHERE ( item_t0.Qualifier = 'CommentItemRelation' AND item_t0.TargetPK = 8796093120556 AND item_t0.languagepk
IS NULL) AND (item_t1.TypePkString IN (8796112748626,8796112879698,
8796112978002,8796112519250) )
order by item_t0.RSequenceNumber ASC , item_t0.PK ASC

There are 73 calls to the database in total! More interestingly, there are 13 ‘commit’ calls among them. Quite a number, isn’t it?

Before understanding how this happened, let’s look into more detail what a persistence operation is for SAP Commerce (Hybris). To keep it simple, for a developer it’s one of the following:

• save() or modelService.saveAll() call, where each item is persisted individually.
• setter() call in the Jalo layer. Each Jalo setter invocation is an independent persistence operation that is instantly flushed to the database.
• Outer transaction begin/commit boundary. When the outer transaction is committed all data that was modified inside this transaction is flushed to the database.

So, each commit from the listing above was triggered by one of these persistence operations. Can we improve this? Yes, we do. The answer is to wrap the operation into a transaction.

Note that inside a transaction things work slightly differently. In particular:

• The so-called delayed store is introduced that accumulates all modifications of all items that are not saved.
• Jalo item.setter() doesn’t flush new values to the database. Instead, it puts it in the delayed store.
• save() flushes the item’s delayed store data to the database, but this modification is still a part of transaction non-committed data for the database and it can be rolled back.

As we see, transactions tend to group persistence operations and reduce the number of updates to the database, especially if Jalo is used. Therefore, we can expect that the number of calls to the database can be reduced when we wrap the code into a transaction.

However, it’s not the full truth. The truth is that there is overhead induced by the Hybris persistence layer on each item save. The following happens behind the scenes on each persistence operation:

• Data is stored in the database.
• Hybris invalidates the item’s cache entry. When you need this item again, even if it is the next line of the code, Hybris will retrieve it from the database again even if a tiny part of the item has been changed.
• Hybris keeps track of the modification history for each item. There are special tables in the database created with item tables that contain the difference between each persisted state of the item.

This particular inefficiency can be eliminated with transactions. Recall the above-mentioned add-to-cart operation. Why did it produce so many calls to the database?

First of all, the cart model and the item model (and other models) are updated many times during this operation. It is not enough to just add a new cart item to the cart. You also need to recalculate the cart total, apply for promotions, merge cart items if there are items that refer to the same SKU, etc. All this modifies models intensively and each modification is followed by model cache invalidation and updating model modification history, many and many times.

Well, it’s all theory, the reality can be different. Let’s check what calls Hybris issues to the database when the same operation is run into a transaction:

boolean success = false;
Transaction tx = Transaction.current();
tx.begin();
try {
final CartModificationData cartModification = cartFacade.addToCart(code, qty);
success = true;
} finally {
if (success) {
tx.commit();
} else {
tx.rollback();
}
}
Here is the database call listing:

1. 1. SELECT PK FROM cartentries item_t0 WHERE ( item_t0.p_order =8796125823019) AND
(item_t0.TypePkString=8796098363474 ) order by item_t0.p_entrynumber ASC, item_t0.createdTS ASC

1. 2. SELECT PK FROM cartentries item_t0 WHERE (( item_t0.p_entrynumber = 0 AND
item_t0.p_order = 8796125823019 )) AND (item_t0.TypePkString=8796098363474 )

1. 3. SELECT PK FROM cartentries item_t0 WHERE ( item_t0.p_order = 8796125823019) AND
(item_t0.TypePkString=8796098363474 ) order by item_t0.p_entrynumber ASC

1. 4. INSERT INTO cartentries ( hjmpTS,PK,createdTS,modifiedTS,TypePkString,
p_baseprice,p_calculated,
p_entrynumber,p_giveaway,p_info,p_order,
p_product,p_quantity,p_rejected,
p_taxvaluesinternal,p_totalprice,p_unit )
VALUES (0,8796125855788,'2019-04-15 18:14:18.341','2019-04-15 18:14:18.341',
8796098363474,0.0,0,0,0,'product "816802" with name "Cyber-shot W55"',8796125823019,8796231958529,
1,0,'[]',0.0,8796093054986)

1. 5. UPDATE cartentries SET hjmpTS = 1 ,modifiedTS='2019-04-15 18:14:18.346',p_baseprice=260.87,
p_calculated=1,
p_discountvaluesinternal='[]',
p_taxvaluesinternal='[<TV<jp-vat-full#5.0#false#12.42#USD>VT>]',
p_totalprice=260.87
WHERE PK = 8796125855788 AND (sealed IS NULL OR sealed=0)

1. 6. SELECT PK FROM cartentries item_t0 WHERE ( item_t0.p_order =8796125823019) AND
(item_t0.TypePkString=8796098363474 ) order by item_t0.p_entrynumber ASC, item_t0.createdTS ASC

1. 7. SELECT * FROM carts WHERE PK='8796125823019'

1. 8. SELECT * FROM props WHERE ITEMPK='8796125823019'

1. 9. UPDATE carts SET hjmpTS = 7 ,modifiedTS='2019-04-15 18:14:18.352',p_subtotal=260.87,
p_totalprice=260.87,p_totaltax=12.42,
p_totaltaxvaluesinternal='[<TV<jp-vat-full#5.0#false#12.42#NULL>VT>]'
WHERE PK = 8796125823019 AND (sealed IS NULL OR sealed=0)

1. 10. SELECT PK FROM promotionresult item_t0 WHERE ( item_t0.p_order =8796125823019) AND
(item_t0.TypePkString=8796113731666 ) order by item_t0.createdTS ASC

1. 11. SELECT PK FROM campaign item_t0 WHERE ( item_t0.p_enabled = 1 AND ( item_t0.p_startdate IS NULL OR
item_t0.p_startdate <= '2019-04-15 18:14:18.362') AND ( item_t0.p_enddate IS NULL OR
item_t0.p_enddate >= '2019-04-15 18:14:18.362')) AND (item_t0.TypePkString=8796118188114 )

1. 12. SELECT PK FROM carts item_t0 WHERE ( item_t0.p_code ='00001000') AND (item_t0.TypePkString=8796094038098 )
UNION ALL SELECT item_t0.PK FROM orders item_t0 WHERE ( item_t0.p_code ='00001000') AND (item_t0.TypePkString IN (8796097609810,8796097675346,8796094169170) )
UNION ALL SELECT item_t0.PK FROM quotes item_t0 WHERE ( item_t0.p_code ='00001000') AND (item_t0.TypePkString=8796094103634 )

1. 13. INSERT INTO promotionresult ( hjmpTS,PK,createdTS,modifiedTS,
TypePkString,p_certainty,p_order,p_promotion )
VALUES (0,8796125860757,'2019-04-15 18:14:18.384','2019-04-15 18:14:18.384',8796113731666,
'1.0',8796125823019,8796094108562)

1. 14. SELECT PK FROM orderentryconsumed item_t0 WHERE ( item_t0.p_promotionresult = 8796125860757)
AND (item_t0.TypePkString=8796114485330 )

1. 15. SELECT * FROM promotionresultlp WHERE ITEMPK=8796125860757 AND LANGPK=8796093055008

1. 16. INSERT INTO promotionresultlp ( p_messagefired,ITEMPK,ITEMTYPEPK,LANGPK )
VALUES ( 'Buy over $200.00 get $20.00 discount on cart',8796125860757,8796113731666,
8796093055008 )

1. 17. UPDATE promotionresult SET hjmpTS = 1 ,modifiedTS='2019-04-15 18:14:18.388',p_moduleversion=23,
p_ordercode='00001000',
p_ruleversion=22,
p_rulesmodulename='promotions-module'
WHERE PK = 8796125860757 AND (sealed IS NULL OR sealed=0)

1. 18. INSERT INTO promotionaction ( hjmpTS,PK,createdTS,modifiedTS,
TypePkString,p_amoun0,p_guid,p_markedapplied,
p_promotionresult,p_rule,p_strategyid )
VALUES (0,8796125860759,'2019-04-15 18:14:18.391','2019-04-15 18:14:18.391',8796114288722,'20.00000',
'Action[20451b5f-2f56-44b5-98b2-f34d9c767aa3]',1,8796125860757,8796093748204,
'defaultOrderAdjustTotalActionStrategy')

1. 19. UPDATE carts SET hjmpTS = 8 ,modifiedTS='2019-04-15 18:14:18.393',p_calculated=0,
p_globaldiscountvaluesinternal='
[<DV<Action[20451b5f-2f56-44b5-98b2-f34d9c767aa3]
#20.0#true#0.0#USD#false>VD>]'
WHERE PK = 8796125823019 AND (sealed IS NULL OR sealed=0)

1. 20. UPDATE carts SET hjmpTS = 9 ,modifiedTS='2019-04-15 18:14:18.397',p_calculated=1,
p_globaldiscountvaluesinternal='
[<DV<Action[20451b5f-2f56-44b5-98b2-f34d9c767aa3]
#20.0#true#20.0#USD#false>VD>]',
p_totaldiscounts=20.0,p_totalprice=240.87,
p_totaltax=11.47,
p_totaltaxvaluesinternal='[<TV<jp-vat-full#5.0#false#11.47#NULL>VT>]'
WHERE PK = 8796125823019 AND (sealed IS NULL OR sealed=0)

1. 21. SELECT PK FROM abstrcfgproductinfo item_t0 WHERE ( item_t0.p_orderentry =8796125855788) AND
(item_t0.TypePkString IN (8796109766738,8796109701202) ) order by item_t0.p_orderentrypos ASC, item_t0.createdTS ASC

1. 22. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093087795)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 23. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093153331)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 24. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093186099)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 25. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093186099)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 26. SELECT PK FROM medias item_t0 WHERE ( item_t0.p_mediacontainer = 8796183887922 AND item_t0.p_mediaformat = 8796093218867)
AND (item_t0.TypePkString IN (8796100296786,8796100001874,8796100198482,
8796099706962,8796100493394,8796099444818,
8796099870802,8796100427858,8796099772498,
8796099805266,8796100034642,8796100132946,
8796099477586,8796099575890,8796099936338,
8796099969106,8796100460626,8796100165714,
8796099510354,8796099543122,8796099641426,
8796099674194,8796093513810) )

1. 27. SELECT PK FROM commentitemrelations item_t0 JOIN comments item_t1 ON item_t0.SourcePK = item_t1.PK
WHERE ( item_t0.Qualifier = 'CommentItemRelation' AND item_t0.TargetPK = 8796125855788 AND item_t0.languagepk
IS NULL) AND (item_t1.TypePkString IN (8796112748626,8796112879698,
8796112978002,8796112519250) )
order by item_t0.RSequenceNumber ASC , item_t0.PK ASC

As we see, the number of calls was reduced from 73 to just 26 and there are no commits at all. The data is committed when the transaction is committed explicitly.

It’s easy to check how much time can be saved on executing a typical business operation inside a transaction compared to running it with no transaction. It depends on the operation and system load, but, to give an outline, during load testing sessions in most cases the same code runs 1.5-10 times faster inside a transaction. As a bonus, this code can become transactional, so that data inconsistency can be resolved inside a transaction and intermediate modification will not be visible outside the transaction.

Conclusion

Don’t be afraid of using a transaction in Hybris. Use them everywhere you need to perform some data modifications atomically and be able to revert the modifications if something goes wrong. In many cases, this code will be not only more robust but also faster than its non-transactional alternative.