Nuwana - Software Development

  @Transactional When You Use @Transactional at the Class Level: All public methods of the class are automatically transactional, as if you had added @Transactional on each method. Public method  ==> Runs within a transaction                                            Private / protected method  ==>  Not transactional (no proxy interception)                         Internal method call  ==>  Not transactional (e.g., calling one method from another within same class)  Spring AOP-based proxy  ==> Only public methods are proxied and eligible for transaction management   With Proxy (via @Transactional): Spring creates this proxy automatically when you use @Transactional. userService.updateUser();  Goes through proxy first:    ...

 Topics  01. Steps in SQL Query Execution 02. How does the database create a query plan 03. How Query Plan Caching Works 04. What is the need for an execution plan? 05. How Do Query/ Query Plan Execute (Inside MySQL Server) 06. Importance Of Undo Log 07. How Row Lock Is Handle When Query On The Table Row 08. Technique to recover the committed changes 09. Auditing technique 10. Query cache 11. DB connection pool 12. What is ACID? 13. Optimizing SQL queries

Steps in SQL Query Execution (MySQL) 1. Client Sends Query to Server The SQL statement is sent to the MySQL server over a connection (e.g., via JDBC or a query tool like MySQL Workbench). 2. Parser / Syntax Check The parser checks the SQL syntax.If there are any syntax errors, it throws an error immediately. 3. Pre-processor Verifies privileges and permissions for the user executing the query. Resolves object names (like table or column aliases). 4. Query Optimization The optimizer evaluates multiple strategies to execute the query. It considers: Which indexes to use. Join order (for multi-table queries). Whether to use temporary tables. Cost of different execution paths. The optimizer chooses select the most efficient query plan (Cost effective -> memory, CPU etc). 5. Query Execution Plan (Most cost effective query plan) The query plan is a blueprint of how MySQL will fetch the data. You can view this with the EXPLAIN keyword in front of your query. 6. Query Execution The storage e...

  How does the database create a query plan? 01. MySQL (and most RDBMS): Execution Plan Selection is Based on Cost Estimation How it works: The query is parsed and analyzed. The query optimizer looks at all possible execution plans (e.g., index scan, full table scan, different join orders). It does NOT run the plans — instead, it uses statistics (like row count, index selectivity, table size) to estimate the cost of each plan. The lowest-cost plan is chosen and executed. Example: SELECT * FROM orders WHERE customer_id = 123 MySQL checks: Is there an index on `customer_id`? How many rows match `customer_id = 123` (using index stats)? What is the estimated disk I/O and CPU cost? It chooses the plan with the lowest estimated cost, not the proven fastest.   02. MongoDB: Execution Plan Selection is Based on Trial Execution How it works: MongoDB generates several candidate plans. It actually runs each plan for a short time — like a "mini test drive" (Actually each plan is running f...

  How Query Plan Caching Works When a SQL query is executed: First Time Execution: The database parses and optimizes the query. It saves the execution plan in memory (query cache). Next Time (Same Query): If the exact same query (including whitespace and case) is executed again, The database reuses the saved plan. This saves CPU usage and makes the query faster   MySQL: MySQL does not cache execution plans for regular SQL queries (unlike Oracle or SQL Server). It only caches plans for Prepared Statements  and Stored Procedures . MongoDB: MongoDB is handle a cache for the all the queries (regular queries, But not it happen in MySQL).   What happens when the  underlying database structure (like indexes) changes after a query plan has been cached When a database index changes (created, dropped, or modified), the cached execution plan might become invalid, suboptimal, or even fail. So the database needs to detect the change and take action. 1. MySQL: MySQL automatic...

  What is the need for an execution plan? Why do databases like MySQL and MongoDB analyze multiple execution plans (or run trial plans) instead of just running the query directly with a default/simple plan? Wouldn’t that be faster? Analyzing plans does take a little extra time initially, but it's usually worth it — because a bad plan can make your query 100x slower or even never finish on large datasets. Think of it like this:  "Spend a few milliseconds choosing the best route, or risk taking a path that takes 10x longer."

  Query/ Query Plan Execution (Inside MySQL Server) This process happens inside the MySQL server When you run:   UPDATE employees SET salary = 50000 WHERE id = 101 ;   MySQL does the following: Step-by-Step Inside MySQL Server Step What Happens Where 1 Parses and optimizes the query MySQL SQL layer 2 Finds the row using index (if available)    InnoDB engine 3 Loads the row into buffer pool (RAM) InnoDB memory buffer 4 Changes the value in memory (not yet on disk) Buffer pool 5 Writes undo log  entry (for rollback) InnoDB undo tablespace 6 Writes redo log  entry (for crash recovery) InnoDB log file 7 At COMMIT , redo log is flushed to disk Log file 8 InnoDB marks changes as committed , visible to others Buffer pool + Transaction system 9 Actual data file on disk is written later (lazily) InnoDB table file   Main steps: In-memory changes in the buffer pool (RAM) : hold the modifying row with modifiying value Undo logs for rollback: Hold the original...