Serializable Carts - Website crash? Wait what?...

Stop and do this right now:

If you are running a transactional ecommerce website on Optimizely please check the health and size of your commerce database in Application Insights. This gets typically overlooked unless you have an alert setup or there's a issue on the website.

Okay maybe I lied in the title. It won't be a website crash but none of the carts can be created (naturally no orders can be created until this issue is resolved). This will be SEV 1 issue with the client yelling on the phone to get this resolved ASAP.

What are Serializable Carts:

Serializable carts have been around for a while so it's not a net new functionality. Documentation here: https://docs.developers.optimizely.com/commerce/v14.0.0-commerce-cloud/docs/serializable-carts

The SerializableCarts table is pretty simple. The data column holds the entire cart as a JSON string (which can use up some good bytes in the DB).

Serializable carts are created/updated in the database every time a user adds/updates an item to the cart (because of SaveCart() calls in your code). Saving carts in the database is a useful piece of functionality for keeping the users' history of products as well as retrieving their saved items even after they close their browser (as long as they don't delete cookies). Seriaziable Carts are also used for 'Wishlist' to create favorites or other lists that are stored in the database.

Anything wrong here?

         public ICart GetInMemoryCart(Guid contactGuid)
        {
            // Load cart to get a fake cart, if not create one
            var cart = _orderRepository.LoadOrCreateCart<ICart>(contactGuid, "Default");

            return cart;
        }

        public void CallingMethodDoPromotionCalculations()
        {
            // Get a fake cart to do custom calculations
            var cart = GetInMemoryCart(Guid.NewGuid());

            if (cart != null)
            {
                // Add line items to the cart
               // Basic shipping address etc.
               // Do promotion calculations on the cart to check if user will qualify
               // More code
                _orderRepository.Save(cart);
            }

           // Return the results of the calculations
        }

What's wrong?

If you couldn't figure out the problem the issue isn't evident at first sight. The above code will create a new cart with a new GUID (meant to be for in-memory calculations etc.) in the database. Now if this code runs (on say home page), every time the user browses to the website (whether they add items to the cart or not) you got a problem.

There are 2 things to watch out for:

1. It's using the LoadOrCreate() creating a cart with a new GUID every time, instead of using something like CustomerContext.Current.CurrentContactId
2. It's calling the SaveCart() which actually commits this cart to the database with the new Customer GUID.

Death by a thousand paper cuts:

Here's a scenario to consider where this issue can go unnoticed for days/months until your database is full:

1. The above piece of code was accidentally introduced on a frequently visited page and it's creating thousands of carts every day. 
2.  Well luckily Optimizely provides a scheduled job called "Remove expired carts" that deletes carts that haven't been modified in the last 30 days and by default runs once every day. 
3. The issue is the scheduled job won't get to these fake/junk carts until 30 days later and in the meanwhile created 1 million carts/rows in the database. 
4. Well we have the scheduled job no issue right? (hopefully!). However, now when the scheduled job tries to get all serializable carts to delete within the last 30 days it may start failing due to timeouts. Then your website is doomed....very slowly :) because if the failing scheduled job goes unnoticed for a while you are accumalating carts and absolutely nothing to purge them. 
5. Soon enough the SerializableCarts table will grow expoentially and eat up all the allocated database space (currently set to 250 GB in DXP)
6. When this happens in production, no new carts will be created. God forbid if it happens during peak traffic hours or even worse on Cyber Monday this will be SEV 1 right away!!!
   
   

Short Term Fix:

1. Call/Email Optimizely customer support and ask them to increase the database size from 250GB to 500 GB. This will resolve the issue right away and your website will be operational and buy you some time to get to the bottom of this issue.

Long term Solution:

1. Check application insights to see how long this has been going on and also get a sense of daily increase in the database size
   
   
2. Ask support to run the following query to get the size of the tables in the ecommerce database. If this is a seriazible cart issue, the table will show up to the top of the list. 
   

   SELECT 
       t.NAME AS TableName,
       s.Name AS SchemaName,
       p.rows,
       SUM(a.total_pages) * 8 AS TotalSpaceKB, 
       CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
       SUM(a.used_pages) * 8 AS UsedSpaceKB, 
       CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
       CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
   FROM 
       sys.tables t
   INNER JOIN      
       sys.indexes i ON t.OBJECT_ID = i.object_id
   INNER JOIN 
       sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
   INNER JOIN 
       sys.allocation_units a ON p.partition_id = a.container_id
   LEFT OUTER JOIN 
       sys.schemas s ON t.schema_id = s.schema_id
   WHERE 
       t.NAME NOT LIKE 'dt%' 
       AND t.is_ms_shipped = 0
       AND i.OBJECT_ID > 255 
   GROUP BY 
       t.Name, s.Name, p.Rows
   ORDER BY 
       TotalSpaceMB DESC, t.Name

3. Ask Optimizely support to run the following script to clean up the ecommerce database SerializableCarts table (PLEASE TEST THIS ON LOCAL AND OTHER LOWER ENVIRONMENTS FIRST)
   

   -- The -300 is the lookback (depends on how far back this issue has been around. The script will need to be run in increments by support -300, -200, -100 down to -30. 
   DELETE from SerializableCart where Modified < DATEADD(day, -300, GETDATE()) and Name != 'WishList'

4. Lower the frequency of the "Remove Expired Carts" scheduled job to run every hour or multiple times in a day (this wouldn't help unless you clean up the database first)
   
   
5. Investigate the root cause with special attention to SaveCart(), LoadOrCreateCart() and LoadCart().
   
   
6. Setup alerts with Optimizely to be notified when the database size increases 20% more than the baseline.
   
   
7. Manually check the scheduled job is functioning regularly to avoid the same problem from happening again. 

Happy coding!