SQL ASENSITIVE

What is SQL ASENSITIVE?

ASENSITIVE tells the database that the sensitivity of a cursor to changes in the underlying data is implementation-defined.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ASENSITIVE: MySQL 5.0+, MariaDB 5.3+, IBM Db2 8+, H2 2.x. Not supported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL ASENSITIVE Full Explanation

ASENSITIVE is a cursor attribute defined in the SQL standard and implemented by MySQL, MariaDB, and IBM Db2. It appears in DECLARE CURSOR statements inside stored programs. A cursor marked ASENSITIVE may or may not reflect changes made to the base tables after the cursor is opened; the decision is left to the database engine. This differs from INSENSITIVE (guaranteed to use a temporary snapshot and ignore later changes) and SENSITIVE (guaranteed to see committed changes). If no attribute is specified, many systems default to ASENSITIVE. Because behavior is not guaranteed, rely on ASENSITIVE only when your logic is unaffected by whether the cursor sees concurrent updates.

SQL ASENSITIVE Syntax

DECLARE cursor_name ASENSITIVE CURSOR FOR
    select_statement;

SQL ASENSITIVE Parameters

Example Queries Using SQL ASENSITIVE

DELIMITER $$
CREATE PROCEDURE recent_orders()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE o_id INT;
    DECLARE o_date DATE;

    DECLARE cur ASENSITIVE CURSOR FOR
        SELECT order_id, order_date
        FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO o_id, o_date;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- process each row here
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

Expected Output Using SQL ASENSITIVE

The procedure compiles, opens a cursor that may or may not see rows inserted or updated after OPEN CURSOR, fetches each row, and then closes the cursor

Use Cases with SQL ASENSITIVE

When cursor sensitivity is irrelevant to business logic and you prefer the database to choose the optimal plan
When you want standard-compliant code that runs on engines where ASENSITIVE is the default
When performance is critical and you allow the optimizer to decide between snapshot or live view

Common Mistakes with SQL ASENSITIVE

Assuming ASENSITIVE will always reflect live changes; it might not
Mixing ASENSITIVE with requirements that demand deterministic sensitivity
Forgetting that some databases (e.g., PostgreSQL, SQL Server) do not recognize the keyword and will raise a syntax error

First Introduced In

SQL:2003 standard; MySQL 5.0

Frequently Asked Questions

What does ASENSITIVE do?

ASENSITIVE leaves the decision about cursor sensitivity to the database engine. The cursor might see or ignore subsequent changes.

Is ASENSITIVE faster than INSENSITIVE?

Often yes, because the optimizer can choose the cheaper strategy. INSENSITIVE forces a temporary snapshot that can add overhead.

Can I combine ASENSITIVE with FOR UPDATE?

Yes, but behavior still depends on the engine. If the engine treats the cursor as INSENSITIVE internally, rows might not be lockable.

How do I know if my cursor behaved sensitively?

Test by updating a row after opening the cursor and before fetching. If the change appears in the fetched results, the cursor was sensitive.