Release	Classification	Level	DB Platform	Categories
3.12 GA	How To		MYSQL	Transaction

What are ScaleArc MySQL transaction patterns and how to configure them?

As part of advanced configuration, ScaleArc provides the option to configure patterns/rules at varying levels of granularity. These patterns/rules determine how ScaleArc caches queries and uses read ignore statements within transaction to load balance traffic. Contact support@scalearc.com for additional details on configuring these features.

Transaction Pattern

Follow the steps below to configure a transaction pattern which will decide the behavior for caching, load balancing, and read ignore for transactions. You can also enable/disable a pattern.

1. Click on the gear icon to add/configure patterns for the databases.
   
    
    
2. Enter the patterns.
   
    
   
   

3. Complete the entries as follows:

   Field/Button	Description	User input
   ADD Pattern button	Opens the related screen. Create a pattern and ensure that it's entered in the order in which you want it executed.
   Order	Specifies the order in which the query should be executed.	Enter the order.
   Rules	Enter the pattern's regular expression.	Enter the rule.
   Time To Live	Defines when the cache expires.	Enter a value when you create a pattern.
   Read Ignore	This option allows ScaleArc to treat matching SQL statements as write instead of read. This applies only in transaction.	Select enabled/ disabled
   Cache	This option allows ScaleArc to perform caching of matching SQL statements in tranasaction.	Select enabled/ disabled
   LB	This option allows ScaleArc to perform load-balancing of matching SQL statements in transaction.	Select enabled/ disabled
   Enabled	Disables or enables a transaction rule from executing, depending on its state. You can enable or disable this option during or after you create the pattern.	The default is enabled.
   Delete icon	You can also click on the delete icon to delete the cache rule.	Click icon to delete

   It is important to include only those read statements in transaction that are not expected to fetch frequently-changing data. Frequently changing SQL statements will get load balanced in a manner where ScaleArc will fetch data from read server as 'READ-COMMITTED' only. For such statements, use pattern management to keep it out of load balancing.

Write Ignore Rules

Follow the steps below to configure stored procedure which will exclude certain MySQL SQL statements from being detected as write statements. 

1. Click on the Write Ignore Rules tab.
   
    
    
2. Click on Add Rule to add Write Ignore rules for the database. Click on Save when done. 
   
    
    
3.  Click on List Stored Procedures to view the list of Write Ignore rules created. 

Unstick Query

Query patterns created here are matched against each SQL statement. If matched, connections will unstick from the existing server connection.

1. Click on the Unstick Query tab.
   
    
    
2. Click on Add Rule to create a query pattern for an unstick query. Click on Save.