CONSISTENCY

Determines how many nodes in the replica must respond for the coordinator node to successfully process a non-lightweight transaction during the CQL shell session.

Consistency level determines how many nodes in the replica must respond for the coordinator node to successfully process a non-lightweight (non-LWT) transaction.

Restriction: The CQL shell only supports read requests (SELECT statements) when the consistency level is set to SERIAL or LOCAL_SERIAL. For more information, see Data consistency. Set the consistency level for a LWT using SERIAL CONSISTENCY. A LWT is a write request that contains IF EXISTS or IF NOT EXISTS statements.

Synopsis

CONSISTENCY [ level ]
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Displaying the current level

Use consistency with no options, to show the current consistency level:
CONSISTENCY
Current consistency level is ONE.

The default CQL shell level is ONE.

Setting a level

Level determines data availability versus data accuracy for transactions during the CQL shell session, some settings also may have high impact other transactions occurring in the cluster, such as ALL and SERIAL. The CQL shell setting supersedes the consistency-level global setting.

Important: Before changing this setting it is important to understand How the database reads and writes data, Data replication, How QUORUM is calculated, and partition keys.

When you initiate a transaction from the CQL shell, the coordinator node is typically the node where you started cqlsh; if you connect to a remote host, then the remote node is the coordinator.

Table 2. Read Consistency Levels
Level Replicas Consistency Availability
ALL All Highest Lowest
EACH_QUORUM Quorum in each datacenter. Same across datacenters
QUORUM Quorum of all nodes across all datacenters. Some level of failure is possible.
LOCAL_QUORUM Quorum of replicas in the same datacenter as the coordinator node. Avoids communication latency between datacenters. Low in multi-datacenter
ONE Closest replica as determined by the snitch. Satisfies the needs of most users because the consistency requirements are not stringent. Lowest (READ) Highest (READ)
TWO Closest two replicas as determined by the snitch.
THREE Closest three replicas as determined by the snitch.
LOCAL_ONE Returns a response from the closest replica in the local datacenter. For security and quality, use in an offline datacenter to prevent automatic connection to online nodes in other datacenters.
ANY Closest replica, as determined by the snitch.

If all replica nodes are down, the write succeeds after a hinted handoff. Provides low latency and guarantees that writes never fail.

Lowest (WRITE) Highest (WRITE)
SERIAL Returns results with the most recent data including an inflight LWT (uncommitted). Commits an inflight LWT as part of the read.

Writes NOT supported.

LOCAL_SERIAL Same as SERIAL, but confined to the datacenter.

Writes NOT supported.

Restriction: The SERIAL and LOCAL_SERIAL consistency levels support read transactions. Those levels do not support writes and generate an error; see the INSERT and UPDATE examples at the end of this section. Use the ANY consistency level if possible.

Examples

Set CONSISTENCY to force the majority of the nodes to respond:

CONSISTENCY QUORUM

Set level to serial for LWT read requests:

CONSISTENCY SERIAL
Consistency level set to SERIAL.
List all winners.
SELECT *
  FROM cycling.race_winners;
The results are as follows:
@ Row 1
---------------+--------------------------------------------------
 race_name     | National Championships South Africa WJ-ITT (CN)
 race_position | 1
 cyclist_name  | {firstname: 'Frances', lastname: 'DU TOUT'}

@ Row 2
---------------+--------------------------------------------------
 race_name     | National Championships South Africa WJ-ITT (CN)
 race_position | 2
 cyclist_name  | {firstname: 'Lynette', lastname: 'BENSON'}

@ Row 3
---------------+--------------------------------------------------
 race_name     | National Championships South Africa WJ-ITT (CN)
 race_position | 3
 cyclist_name  | {firstname: 'Anja', lastname: 'GERBER'}

@ Row 4
---------------+--------------------------------------------------
 race_name     | National Championships South Africa WJ-ITT (CN)
 race_position | 4
 cyclist_name  | {firstname: 'Ame', lastname: 'VENTER'}

@ Row 5
---------------+--------------------------------------------------
 race_name     | National Championships South Africa WJ-ITT (CN)
 race_position | 5
 cyclist_name  | {firstname: 'Danielle', lastname: 'VAN NIEKERK'}

(5 rows)
Note: The previous query format uses expand ON for legibility.

Inserts with CONSISTENCY SERIAL fail:

INSERT INTO cycling.race_winners (
  race_name, 
  race_position,
  cyclist_name 
) VALUES (
  'National Championships South Africa WJ-ITT (CN)',
  7,
  { firstname: 'Joe', lastname: 'Anderson' } 
) 
IF NOT EXISTS;
InvalidRequest: Error from server: code=2200 [Invalid query] 
message="SERIAL is not supported as conditional update commit 
consistency. Use ANY if you mean "make sure it is accepted but I don't care 
how many replicas commit it for non-SERIAL reads""

Updates with CONSISTENCY SERIAL also fail:

UPDATE cycling.race_winners SET 
  cyclist_name = { firstname: 'JOHN', lastname: 'DOE' } 
WHERE 
  race_name='National Championships South Africa WJ-ITT (CN)' 
  AND race_position = 6 
IF EXISTS;
InvalidRequest: Error from server: code=2200 [Invalid query]
message="SERIAL is not supported as conditional update commit
consistency. Use ANY if you mean "make sure it is accepted but I don't care
how many replicas commit it for non-SERIAL reads""

Omitting the IF clause generates errors. Using CONSISTENCY LOCAL_SERIAL generates errors.