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.
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> ]
Syntax legend
Syntax conventions | Description |
---|---|
UPPERCASE |
Literal keyword. |
Lowercase |
Not literal. |
|
Variable value. Replace with a user-defined value. |
|
Optional.
Square brackets ( |
|
Group.
Parentheses ( |
|
Or.
A vertical bar ( |
|
Repeatable.
An ellipsis ( |
|
Single quotation ( |
|
Map collection.
Braces ( |
Set, list, map, or tuple.
Angle brackets ( |
|
|
End CQL statement.
A semicolon ( |
|
Separate the command line options from the command arguments with two hyphens ( |
|
Search CQL only: Single quotation marks ( |
|
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.
Display the current consistency level
To show the current consistency level, use the CONSISTENCY command with no options.
CONSISTENCY
Current consistency level is ONE.
Set the 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.
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.
Write consistency levels
Level | Description | Usage |
---|---|---|
|
All replica nodes must acknowledge the write. |
This write consistency level provides the highest consistency, the highest latency, and the lowest availability of any level. |
|
A quorum of replica nodes across all datacenters must acknowledge the write. |
Cross-datacenter communication may incur extra latency. |
|
At least one replica node must acknowledge the write. |
Use for high availability and low consistency. Note: Astra DB does not support write consistency level |
|
At least two replica nodes must acknowledge the write. |
Similar to |
|
At least three replica nodes must acknowledge the write. |
Similar to |
|
At least one replica node must acknowledge the write, or if no replica nodes are available, a coordinator node must store a hint. If all replica nodes are down at write time, the data will not be available until the replica nodes for that partition have recovered. |
This write consistency level provides the lowest latency, the highest write availability, and the lowest consistency. Note: Astra DB does not support write consistency level |
|
A quorum of replica nodes in the local datacenter must acknowledge the write. Avoids latency of cross-datacenter communication. |
Use |
|
A quorum of replica nodes in the each datacenter must acknowledge the write. |
Use |
|
At least one replica node in the local datacenter must acknowledge the write. |
Use Note: Astra DB does not support write consistency level |
|
Write consistency level |
Use to achieve linearizable consistency for lightweight transactions. |
|
Write consistency level |
Use to achieve linearizable consistency for lightweight transactions. |
Read consistency levels
Level | Description | Usage |
---|---|---|
|
Queries return the most recent data from all replica nodes in the cluster. All replica nodes must must respond. |
This read consistency level provides the highest consistency, the highest latency, and the lowest availability of any level. |
|
Queries return the most recent data from a quorum of replica nodes across all datacenters. |
Cross-datacenter communication may incur extra latency. |
|
Queries return data from the closest replica. |
Use for high availability and low consistency. |
|
Queries return the most recent data from two of the closest replicas. Two replica nodes must respond. |
Similar to |
|
Queries return the most recent data from three of the closest replicas. Three replica nodes must respond. |
Similar to |
|
Queries returns the most recent data from a quorum of replicas in the current datacenter.
|
Use |
|
Queries return the most recent data from a quorum of replica nodes in each datacenter has responded. |
Use |
|
Queries return data from the closest replica node in the local datacenter. |
Use |
|
Read consistency level |
Use to achieve linearizable consistency for lightweight transactions. |
|
Read consistency level |
Use to achieve linearizable consistency for lightweight transactions. |
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)
The previous query format uses |
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.