1Z0-909 Free Sample Questions

A financial services application is experiencing deadlocks during high-concurrency periods. The problematic transaction involves updating a user's balance and inserting a record into a transaction log table. The database uses the default REPEATABLE READ isolation level. Analysis reveals that two concurrent transactions often attempt to lock the same range of rows in the transaction log table, which is indexed by `transaction_date`. Which strategy is most effective at resolving these deadlocks while maintaining data consistency?

A developer is building a feature to store user preferences as JSON objects in a `user_profiles` table. The application needs to update a specific nested attribute (`theme.color`) and add a new attribute (`notifications.enabled`) in a single atomic operation. The existing JSON is: `{"theme": {"color": "dark"}, "font_size": 14}`. Which MySQL function should be used to achieve this?

A data analyst needs to generate a report showing the total sales for each product category, but only for categories with total sales exceeding $10,000. Which of the following query structures is correct? ```mermaid erDiagram PRODUCTS ||--o{ ORDER_ITEMS : contains CATEGORIES ||--o{ PRODUCTS : belongs to CATEGORIES { int category_id PK string category_name } PRODUCTS { int product_id PK int category_id FK string product_name decimal price } ORDER_ITEMS { int item_id PK int product_id FK int quantity } ```

You are tasked with optimizing a slow query that retrieves user information. The `EXPLAIN` output shows that a full table scan is performed on the `users` table despite an index existing on the `email` column. The query is: `SELECT user_id, name FROM users WHERE YEAR(created_at) = 2023 AND SUBSTRING(email, INSTR(email, '@') + 1) = 'example.com';`. What is the primary reason the index on `email` is not being used?

A developer needs to create a stored procedure that accepts an employee ID and returns their department name and manager's name. Which parameter modes should be used for the department name and manager's name? (Select TWO)

True or False: In MySQL 8.0, using the `JSON_TABLE()` function, you can project a JSON document into a relational table format within a single query, which can then be joined with other standard relational tables.

An e-commerce company, "GlobalCart," is migrating its product catalog to a MySQL 8.0 database. The catalog data for each product is semi-structured and received from various suppliers as JSON documents. A key requirement is to allow flexible schema changes without database migrations, while also supporting high-performance filtering on specific attributes like `price` and `brand_id` which are nested deep within the JSON. The development team is also building a new set of microservices that will interact with this data using modern, fluent APIs rather than raw SQL strings. The current table is defined as `CREATE TABLE products (id INT PRIMARY KEY, doc JSON);`. Initial performance tests show that queries filtering on price, such as `SELECT * FROM products WHERE JSON_EXTRACT(doc, '$.details.price') < 50;`, are very slow because they require a full table scan and JSON parsing for every row. Which solution best meets GlobalCart's requirements for schema flexibility, query performance, and modern API access?

A developer needs to connect a new Python application to a MySQL 8.0 database. The requirements are to use an official, pure Python driver that supports the new X Protocol for Document Store access. Which connector should be chosen?

You need to design a view that summarizes customer order totals. The view should prevent any direct `INSERT` or `UPDATE` operations that would result in a customer having a negative total order value. How can this constraint be enforced through the view definition?

A batch import process is inserting millions of rows into an InnoDB table within a single transaction. The process is consuming excessive memory and UNDO log space, occasionally causing the server to run out of resources. Which TWO actions can mitigate this issue without sacrificing the all-or-nothing nature of the import? (Select TWO)