CONSISTENCY

Sets and displays the consistency level. The consistency level determines the number of replica nodes that must respond for the coordinator node to successfully process a non-lightweight transaction (non-LWT).

The CQL shell supports only read requests (SELECT statements) when the consistency level is set to SERIAL or LOCAL_SERIAL. See Data consistency in the documentation.

Tip: To set the consistency level of a lightweight transaction (LWT), use the SERIAL CONSISTENCY command. When using a LWT, you must have both a CONSISTENCY and a SERIAL CONSISTENCY level set. CONSISTENCY cannot be set to SERIAL or LOCAL_SERIAL, only SERIAL CONSISTENCY can be set to SERIAL or LOCAL_SERIAL.

Synopsis

CONSISTENCY [ 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.

consistency_level: The consistency level. See the documentation for the list of valid levels and their description, which is extensive.

Displaying the current consistency level

To show the current consistency level, use the CONSISTENCY command with no options.

CONSISTENCY

Current consistency level is ONE.

The default consistency level is ONE.

Setting a consistency level

The consistency 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 overrides the consistency-level global setting.

Important: Before changing this setting it is important to understand these topics in the documentation: How the database reads and writes data, Data replication, How QUORUM is calculated, and partition keys. See the documentation for the links.

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, 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 the CONSISTENCY QUORUM level to force the majority of the nodes to respond:

CONSISTENCY QUORUM

Set the level to serial for LWT read requests:

CONSISTENCY SERIAL

Consistency level set to SERIAL.

Query that returns all cycling race winners:

SELECT *
FROM cycling.race_winners;

The query 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.

A LWT is a write request that contains IF EXISTS or IF NOT EXISTS statements. To set the consistency level of LWTs, see SERIAL CONSISTENCY. When using a LWT, you must have both a CONSISTENCY and a SERIAL CONSISTENCY level set. CONSISTENCY cannot be set to SERIAL or LOCAL_SERIAL, only SERIAL CONSISTENCY can be set to SERIAL or LOCAL_SERIAL.

The following examples show LWT failure scenarios. Inserts for a LWT with CONSISTENCY SERIAL fail:

CONSISTENCY SERIAL

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 for a LWT with CONSISTENCY SERIAL also fail:

CONSISTENCY SERIAL

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. Also, using CONSISTENCY LOCAL_SERIAL generates the errors as that consistency level results in an invalid request.