Consistency Level
Consistency level determines how many nodes in the replica must respond for the coordinator node to successfully process a non-lightweight transaction.
The default CQL shell level is ONE.
cqlsh
CONSISTENCY
Current consistency 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 Cassandra consistency level global setting.
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.
Read Consistency Levels
| Level | Replicas | Consistency | Availability |
|---|---|---|---|
| ALL | All | Highest | Lowest |
| EACH_QUORUM | Quorum in each data centers.Reads NOT supported. | Same across data centers | |
| QUORUM | Quorum of all nodes across all data centers. Some level of failure. | ||
| LOCAL_QUORUM | Quorum of replicas in the same data center as the coordinator. Avoids latency of inter-datacenter communication. | Low in multi-data center | |
| ONE | Closest replica as determined by the snitch. Satisfies the needs of most users because 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 data center. For security and quality, use in an off line 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, guarantees writes never fail. | Lowest (WRITE) | Highest (WRITE) |
| SERIAL | Returns results with the most recent data including inflight LWT (uncommitted). Commits inflight LWT as part of the read. Writes NOT supported. | ||
| LOCAL_SERIAL | Same as SERIAL, but confined to the data center. Writes NOT supported. |
Restriction: CQL shell only supports read requests (SELECT statements) when the consistency level is set to SERIAL or LOCAL_SERIAL.
Examples
Set CONSISTENCY to force the majority of the nodes to respond:
cqlsh
CONSISTENCY QUORUM
Consistency level set to QUORUM.
Set level to serial for LWT read requests:
CONSISTENCY SERIAL
Consistency level set to SERIAL.
List all winners.
expand ON;
Now Expanded output is enabled
SELECT * FROM "status_update_replies" WHERE "status_update_username" = 'alice' AND "status_update_id" = 97719c50-e797-11e3-90ce-5f98e903bf02;
@ Row 1
------------------------+--------------------------------------
status_update_username | alice
status_update_id | 97719c50-e797-11e3-90ce-5f98e903bf02
id | b98df670-8014-11e8-bbfe-55b594efc7b0
author_username | bob
body | Reply2
(1 rows)
INSERT INTO "status_update_replies" ("status_update_username",
"status_update_id", "id", "author_username", "body")
VALUES ('alice', 76e7a4d0-e796-11e3-90ce-5f98e903bf02, NOW(), 'bob',
'Reply1');
InvalidRequest: Error from server: code=2200 [Invalid query] message="You must use conditional updates for serializable writes"
Testing performance impact
Tracing records all activity related to a request. These steps use tracing to show queries on a keyspace with a replication factor of 3 using different consistency levels (CL):
- ONE processes responses from one of three replicas
- QUORUM from two of three replicas
- ALL from three of three replicas
cqlsh
CREATE KEYSPACE cycling_alt WITH replication = {'class':'SimpleStrategy', 'replication_factor':3};
USE cycling_alt;
CREATE TABLE cycling_alt.tester (id int PRIMARY KEY, col1 int, col2 int);
INSERT INTO cycling_alt.tester(id, col1, col2) VALUES(0, 0, 0);
InvalidRequest: Error from server: code=2200 [Invalid query] message="You must use conditional updates for serializable writes"
CONSISTENCY ONE;
Consistency level set to ONE.
INSERT INTO cycling_alt.tester(id, col1, col2) VALUES(0, 0, 0);
TRACING on;
CONSISTENCY;
Current consistency level is ONE.
SELECT * FROM cycling_alt.tester WHERE id = 0;
id | col1 | col2
----+------+------
0 | 0 | 0
(1 rows)
Tracing session: 2cc8f8b0-801f-11e8-bbfe-55b594efc7b0
activity | timestamp | source | source_elapsed | client
-----------------------------------------------------------------------------------------------------+----------------------------+----------------+----------------+---------------
Execute CQL3 query | 2018-07-05 06:46:41.851000 | 159.65.156.47 | 0 | 159.65.156.47
Parsing SELECT * FROM cycling_alt.tester WHERE id = 0; [Native-Transport-Requests-1] | 2018-07-05 06:46:41.852000 | 159.65.156.47 | 632 | 159.65.156.47
READ message received from /159.65.156.47 [MessagingService-Incoming-/159.65.156.47] | 2018-07-05 06:46:41.852000 | 159.65.146.132 | 47 | 159.65.156.47
Preparing statement [Native-Transport-Requests-1] | 2018-07-05 06:46:41.852000 | 159.65.156.47 | 862 | 159.65.156.47
Executing single-partition query on tester [ReadStage-2] | 2018-07-05 06:46:41.852000 | 159.65.146.132 | 450 | 159.65.156.47
speculating read retry on /159.65.146.132 [Native-Transport-Requests-1] | 2018-07-05 06:46:41.852000 | 159.65.156.47 | 1362 | 159.65.156.47
Acquiring sstable references [ReadStage-2] | 2018-07-05 06:46:41.852000 | 159.65.146.132 | 622 | 159.65.156.47
Merging memtable contents [ReadStage-2] | 2018-07-05 06:46:41.852000 | 159.65.146.132 | 685 | 159.65.156.47
Executing single-partition query on tester [ReadStage-2] | 2018-07-05 06:46:41.853000 | 159.65.156.47 | 1458 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-2] | 2018-07-05 06:46:41.853000 | 159.65.146.132 | 1157 | 159.65.156.47
Acquiring sstable references [ReadStage-2] | 2018-07-05 06:46:41.853000 | 159.65.156.47 | 1676 | 159.65.156.47
Enqueuing response to /159.65.156.47 [ReadStage-2] | 2018-07-05 06:46:41.853000 | 159.65.146.132 | 1257 | 159.65.156.47
Merging memtable contents [ReadStage-2] | 2018-07-05 06:46:41.853000 | 159.65.156.47 | 1770 | 159.65.156.47
Sending REQUEST_RESPONSE message to /159.65.156.47 [MessagingService-Outgoing-/159.65.156.47-Small] | 2018-07-05 06:46:41.853000 | 159.65.146.132 | 1453 | 159.65.156.47
Sending READ message to /159.65.146.132 [MessagingService-Outgoing-/159.65.146.132-Small] | 2018-07-05 06:46:41.853000 | 159.65.156.47 | 1923 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-2] | 2018-07-05 06:46:41.853000 | 159.65.156.47 | 2052 | 159.65.156.47
REQUEST_RESPONSE message received from /159.65.146.132 [MessagingService-Incoming-/159.65.146.132] | 2018-07-05 06:46:41.856000 | 159.65.156.47 | 26 | 159.65.156.47
Processing response from /159.65.146.132 [RequestResponseStage-1] | 2018-07-05 06:46:41.856000 | 159.65.156.47 | 360 | 159.65.156.47
Initiating read-repair [RequestResponseStage-1] | 2018-07-05 06:46:41.856000 | 159.65.156.47 | 516 | 159.65.156.47
Request complete | 2018-07-05 06:46:41.853363 | 159.65.156.47 | 2363 | 159.65.156.47
Change the consistency level to QUORUM to trace what happens during a read with a QUORUM consistency level.
CONSISTENCY quorum;
Consistency level set to QUORUM.
SELECT * FROM cycling_alt.tester WHERE id = 0;
id | col1 | col2
----+------+------
0 | 0 | 0
(1 rows)
Tracing session: 4dfc4500-801f-11e8-bbfe-55b594efc7b0
activity | timestamp | source | source_elapsed | client
-----------------------------------------------------------------------------------------------------+----------------------------+----------------+----------------+---------------
Execute CQL3 query | 2018-07-05 06:47:37.552000 | 159.65.156.47 | 0 | 159.65.156.47
Parsing SELECT * FROM cycling_alt.tester WHERE id = 0; [Native-Transport-Requests-1] | 2018-07-05 06:47:37.553000 | 159.65.156.47 | 406 | 159.65.156.47
READ message received from /159.65.156.47 [MessagingService-Incoming-/159.65.156.47] | 2018-07-05 06:47:37.553000 | 159.65.146.132 | 24 | 159.65.156.47
Preparing statement [Native-Transport-Requests-1] | 2018-07-05 06:47:37.553000 | 159.65.156.47 | 640 | 159.65.156.47
Executing single-partition query on tester [ReadStage-3] | 2018-07-05 06:47:37.553000 | 159.65.146.132 | 796 | 159.65.156.47
reading digest from /159.65.146.132 [Native-Transport-Requests-1] | 2018-07-05 06:47:37.553000 | 159.65.156.47 | 1197 | 159.65.156.47
Acquiring sstable references [ReadStage-3] | 2018-07-05 06:47:37.553000 | 159.65.146.132 | 916 | 159.65.156.47
Executing single-partition query on tester [ReadStage-2] | 2018-07-05 06:47:37.553000 | 159.65.156.47 | 1249 | 159.65.156.47
Merging memtable contents [ReadStage-3] | 2018-07-05 06:47:37.553000 | 159.65.146.132 | 961 | 159.65.156.47
speculating read retry on /159.89.167.247 [Native-Transport-Requests-1] | 2018-07-05 06:47:37.554000 | 159.65.156.47 | 1376 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-3] | 2018-07-05 06:47:37.554000 | 159.65.146.132 | 1643 | 159.65.156.47
Acquiring sstable references [ReadStage-2] | 2018-07-05 06:47:37.554000 | 159.65.156.47 | 1441 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-3] | 2018-07-05 06:47:37.554000 | 159.65.146.132 | 1799 | 159.65.156.47
Merging memtable contents [ReadStage-2] | 2018-07-05 06:47:37.554000 | 159.65.156.47 | 1527 | 159.65.156.47
Enqueuing response to /159.65.156.47 [ReadStage-3] | 2018-07-05 06:47:37.554000 | 159.65.146.132 | 1858 | 159.65.156.47
Sending READ message to /159.89.167.247 [MessagingService-Outgoing-/159.89.167.247-Small] | 2018-07-05 06:47:37.554000 | 159.65.156.47 | 1578 | 159.65.156.47
Sending READ message to /159.65.146.132 [MessagingService-Outgoing-/159.65.146.132-Small] | 2018-07-05 06:47:37.554000 | 159.65.156.47 | 1734 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-2] | 2018-07-05 06:47:37.554000 | 159.65.156.47 | 1812 | 159.65.156.47
READ message received from /159.65.156.47 [MessagingService-Incoming-/159.65.156.47] | 2018-07-05 06:47:37.555000 | 159.89.167.247 | 31 | 159.65.156.47
Sending REQUEST_RESPONSE message to /159.65.156.47 [MessagingService-Outgoing-/159.65.156.47-Small] | 2018-07-05 06:47:37.555000 | 159.65.146.132 | 2315 | 159.65.156.47
Executing single-partition query on tester [ReadStage-1] | 2018-07-05 06:47:37.555000 | 159.89.167.247 | 431 | 159.65.156.47
Acquiring sstable references [ReadStage-1] | 2018-07-05 06:47:37.555000 | 159.89.167.247 | 615 | 159.65.156.47
Merging memtable contents [ReadStage-1] | 2018-07-05 06:47:37.555000 | 159.89.167.247 | 680 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-1] | 2018-07-05 06:47:37.556000 | 159.89.167.247 | 1005 | 159.65.156.47
Enqueuing response to /159.65.156.47 [ReadStage-1] | 2018-07-05 06:47:37.556000 | 159.89.167.247 | 1106 | 159.65.156.47
Sending REQUEST_RESPONSE message to /159.65.156.47 [MessagingService-Outgoing-/159.65.156.47-Small] | 2018-07-05 06:47:37.556000 | 159.89.167.247 | 1290 | 159.65.156.47
REQUEST_RESPONSE message received from /159.89.167.247 [MessagingService-Incoming-/159.89.167.247] | 2018-07-05 06:47:37.557000 | 159.65.156.47 | 4691 | 159.65.156.47
Processing response from /159.89.167.247 [RequestResponseStage-3] | 2018-07-05 06:47:37.557000 | 159.65.156.47 | 5028 | 159.65.156.47
REQUEST_RESPONSE message received from /159.65.146.132 [MessagingService-Incoming-/159.65.146.132] | 2018-07-05 06:47:37.560000 | 159.65.156.47 | 23 | 159.65.156.47
Processing response from /159.65.146.132 [RequestResponseStage-3] | 2018-07-05 06:47:37.560000 | 159.65.156.47 | 496 | 159.65.156.47
Initiating read-repair [RequestResponseStage-3] | 2018-07-05 06:47:37.561000 | 159.65.156.47 | 669 | 159.65.156.47
Request complete | 2018-07-05 06:47:37.558208 | 159.65.156.47 | 6208 | 159.65.156.47
Change the consistency level to ALL and run the SELECT statement again.
CONSISTENCY ALL;
Consistency level set to ALL.
SELECT * FROM cycling_alt.tester WHERE id = 0;
id | col1 | col2
----+------+------
0 | 0 | 0
(1 rows)
Tracing session: 6b693a80-801f-11e8-bbfe-55b594efc7b0
activity | timestamp | source | source_elapsed | client
-----------------------------------------------------------------------------------------------------+----------------------------+----------------+----------------+---------------
Execute CQL3 query | 2018-07-05 06:48:26.920000 | 159.65.156.47 | 0 | 159.65.156.47
Parsing SELECT * FROM cycling_alt.tester WHERE id = 0; [Native-Transport-Requests-1] | 2018-07-05 06:48:26.921000 | 159.65.156.47 | 347 | 159.65.156.47
Preparing statement [Native-Transport-Requests-1] | 2018-07-05 06:48:26.921000 | 159.65.156.47 | 563 | 159.65.156.47
READ message received from /159.65.156.47 [MessagingService-Incoming-/159.65.156.47] | 2018-07-05 06:48:26.922000 | 159.65.146.132 | 45 | 159.65.156.47
Executing single-partition query on tester [ReadStage-1] | 2018-07-05 06:48:26.922000 | 159.65.146.132 | 424 | 159.65.156.47
Acquiring sstable references [ReadStage-1] | 2018-07-05 06:48:26.922000 | 159.65.146.132 | 534 | 159.65.156.47
Merging memtable contents [ReadStage-1] | 2018-07-05 06:48:26.922000 | 159.65.146.132 | 588 | 159.65.156.47
Read-repair DC_LOCAL [Native-Transport-Requests-1] | 2018-07-05 06:48:26.923000 | 159.65.156.47 | 2586 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-1] | 2018-07-05 06:48:26.923000 | 159.65.146.132 | 927 | 159.65.156.47
reading digest from /159.65.146.132 [Native-Transport-Requests-1] | 2018-07-05 06:48:26.923000 | 159.65.156.47 | 2872 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-1] | 2018-07-05 06:48:26.923000 | 159.65.146.132 | 999 | 159.65.156.47
Executing single-partition query on tester [ReadStage-2] | 2018-07-05 06:48:26.923000 | 159.65.156.47 | 2960 | 159.65.156.47
Enqueuing response to /159.65.156.47 [ReadStage-1] | 2018-07-05 06:48:26.923000 | 159.65.146.132 | 1061 | 159.65.156.47
reading digest from /159.89.167.247 [Native-Transport-Requests-1] | 2018-07-05 06:48:26.923000 | 159.65.156.47 | 3051 | 159.65.156.47
Acquiring sstable references [ReadStage-2] | 2018-07-05 06:48:26.924000 | 159.65.156.47 | 3129 | 159.65.156.47
Sending REQUEST_RESPONSE message to /159.65.156.47 [MessagingService-Outgoing-/159.65.156.47-Small] | 2018-07-05 06:48:26.924000 | 159.65.146.132 | 1758 | 159.65.156.47
Sending READ message to /159.65.146.132 [MessagingService-Outgoing-/159.65.146.132-Small] | 2018-07-05 06:48:26.924000 | 159.65.156.47 | 3148 | 159.65.156.47
Merging memtable contents [ReadStage-2] | 2018-07-05 06:48:26.924000 | 159.65.156.47 | 3220 | 159.65.156.47
Sending READ message to /159.89.167.247 [MessagingService-Outgoing-/159.89.167.247-Small] | 2018-07-05 06:48:26.924001 | 159.65.156.47 | 3494 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-2] | 2018-07-05 06:48:26.924001 | 159.65.156.47 | 3596 | 159.65.156.47
READ message received from /159.65.156.47 [MessagingService-Incoming-/159.65.156.47] | 2018-07-05 06:48:26.925000 | 159.89.167.247 | 27 | 159.65.156.47
Executing single-partition query on tester [ReadStage-1] | 2018-07-05 06:48:26.927000 | 159.89.167.247 | 1793 | 159.65.156.47
REQUEST_RESPONSE message received from /159.65.146.132 [MessagingService-Incoming-/159.65.146.132] | 2018-07-05 06:48:26.927000 | 159.65.156.47 | 7000 | 159.65.156.47
Acquiring sstable references [ReadStage-1] | 2018-07-05 06:48:26.927000 | 159.89.167.247 | 1912 | 159.65.156.47
Merging memtable contents [ReadStage-1] | 2018-07-05 06:48:26.927000 | 159.89.167.247 | 1982 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-1] | 2018-07-05 06:48:26.927000 | 159.89.167.247 | 2437 | 159.65.156.47
Read 1 live rows and 0 tombstone cells [ReadStage-1] | 2018-07-05 06:48:26.927000 | 159.89.167.247 | 2539 | 159.65.156.47
Enqueuing response to /159.65.156.47 [ReadStage-1] | 2018-07-05 06:48:26.927000 | 159.89.167.247 | 2643 | 159.65.156.47
Sending REQUEST_RESPONSE message to /159.65.156.47 [MessagingService-Outgoing-/159.65.156.47-Small] | 2018-07-05 06:48:26.928000 | 159.89.167.247 | 3106 | 159.65.156.47
Processing response from /159.65.146.132 [RequestResponseStage-2] | 2018-07-05 06:48:26.928000 | 159.65.156.47 | 7508 | 159.65.156.47
REQUEST_RESPONSE message received from /159.89.167.247 [MessagingService-Incoming-/159.89.167.247] | 2018-07-05 06:48:26.928000 | 159.65.156.47 | 8004 | 159.65.156.47
Processing response from /159.89.167.247 [RequestResponseStage-2] | 2018-07-05 06:48:26.929000 | 159.65.156.47 | 8186 | 159.65.156.47
Request complete | 2018-07-05 06:48:26.929430 | 159.65.156.47 | 9430 | 159.65.156.47
Summary
| CL | time |
|---|---|
| ONE | 2363 |
| QUORUM | 6208 |
| ALL | 9430 |