====================================================
How does ScyllaDB LWT Differ from Apache Cassandra ?
====================================================

ScyllaDB is making an effort to be compatible with Cassandra, down to the level of limitations of the implementation. 
How is it different?

* ScyllaDB most commonly uses fewer rounds than Cassandra to complete a lightweight transaction. While Cassandra issues a separate read query to fetch the old record, scylla piggybacks the read result on the response to the prepare round.
* ScyllaDB will automatically use synchronous commit log write mode for all lightweight transaction writes. Before a lightweight transaction completes, scylla will ensure that the data in it has hit the device. This is done in all commitlog_sync modes.
* Conditional statements return a result set, and unlike Cassandra, ScyllaDB result set metadata doesn’t change from execution to execution: ScyllaDB always returns  the old version of the  row, regardless of whether the condition is true or not. This ensures conditional statements work well with prepared statements.
* For batch statement, the returned result set contains an old row for every conditional statement in the batch, in statement order. Cassandra returns results in clustering key order.
* For batch statement, ScyllaDB allows mixing `IF EXISTS`, `IF NOT EXISTS`, and other conditions for the same row.
* Unlike Cassandra, ScyllaDB  uses per-core data partitioning, so the RPC  that is done to perform a transaction talks directly to the right core on a peer replica, avoiding the concurrency overhead. This is,  of course, true, if ScyllaDB’s own shard-aware driver is used - otherwise we  add an extra hop to the right core at the coordinator node.
* ScyllaDB does not store  hints for lightweight transaction writes, since this is redundant as all such writes are already present in system.paxos table.


More on :doc:`Lightweight Transactions (LWT) </features/lwt>`

Additional Notes
================

Mixing LWT IF clauses in BATCH statements
-----------------------------------------

`Conditional batches` are `BATCH` statements that contain one or more conditional statements. A batch is executed only if all conditions in all statements are true, and all conditions are evaluated against the initial database state. ScyllaDB allows using different conditions such as `IF EXISTS`, `IF NOT EXISTS`, and other `IF` expressions within the same batch, even if the statements affect the same row.

For example, the following batch statement is valid in ScyllaDB and will be applied successfully:

.. code-block:: cql

   BEGIN BATCH
       UPDATE movies.nowshowing SET main_actor = NULL WHERE movie = 'Invisible Man' IF director = 'Leigh Whannell'
       UPDATE movies.nowshowing SET released = NULL WHERE movie = 'Invisible Man' IF EXISTS
   APPLY BATCH;

    [applied] | movie         | location | run_day | run_time | director       | main_actor     | released   | theater
   -----------+---------------+----------+---------+----------+----------------+----------------+------------+---------
         True | Invisible Man |     null |    null |     null | Leigh Whannell | Elisabeth Moss | 2022-04-06 |    null
         True | Invisible Man |     null |    null |     null | Leigh Whannell | Elisabeth Moss | 2022-04-06 |    null

By contrast, Cassandra does not allow mixing `IF EXISTS`, `IF NOT EXISTS`, and other conditions for the same row:

.. code-block:: cql

   BEGIN BATCH
       UPDATE movies.nowshowing SET main_actor = NULL WHERE movie = 'Invisible Man' IF director = 'Leigh Whannell'
       UPDATE movies.nowshowing SET released = NULL WHERE movie = 'Invisible Man' IF EXISTS
   APPLY BATCH;

   InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot mix IF conditions and IF EXISTS for the same row"

Moreover, ScyllaDB does not return an error even if the conditions are impossible to satisfly, i.e. a batch contains `IF EXISTS` and `IF NOT EXISTS` clauses for the same row. The following query will be executed, though not applied, as the conditions are impossible to satisfy:

.. code-block:: cql

    BEGIN BATCH
        INSERT INTO movies.nowshowing (movie, location, theater, run_day, run_time) VALUES ('Invisible Man', 'Times Square', 'AMC Empire 25', 'Saturday', '23:00:00') IF NOT EXISTS
        UPDATE movies.nowshowing SET theater = NULL WHERE movie = 'Invisible Man' AND location = 'Times Square' AND run_day = 'Saturday' AND run_time = '23:00:00' IF EXISTS
    APPLY BATCH;

    [applied] | movie | location | run_day | run_time | director | main_actor | released | theater
    -----------+-------+----------+---------+----------+----------+------------+----------+---------
        False |  null |     null |    null |     null |     null |       null |     null |    null
        False |  null |     null |    null |     null |     null |       null |     null |    null

In comparison, Cassandra returns an error in this case:

.. code-block:: cql

    BEGIN BATCH
        INSERT INTO movies.nowshowing (movie, location, theater, run_day, run_time) VALUES ('Invisible Man', 'Times Square', 'AMC Empire 25', 'Saturday', '23:00:00') IF NOT EXISTS
        UPDATE movies.nowshowing SET theater = NULL WHERE movie = 'Invisible Man' AND location = 'Times Square' AND run_day = 'Saturday' AND run_time = '23:00:00' IF EXISTS
    APPLY BATCH;

    InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot mix IF EXISTS and IF NOT EXISTS conditions for the same row"