{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176/events","html_url":"https://github.com/cockroachdb/cockroach/issues/23176","id":300768606,"number":23176,"title":"1.1.5: Issue updating row - index seems broken, and select queries shows inconsistent results / duplicate rows with wrong data","user":{"login":"bsnyder788","id":3028415,"avatar_url":"https://avatars3.githubusercontent.com/u/3028415?v=4","gravatar_id":"","url":"https://api.github.com/users/bsnyder788","html_url":"https://github.com/bsnyder788","followers_url":"https://api.github.com/users/bsnyder788/followers","following_url":"https://api.github.com/users/bsnyder788/following{/other_user}","gists_url":"https://api.github.com/users/bsnyder788/gists{/gist_id}","starred_url":"https://api.github.com/users/bsnyder788/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bsnyder788/subscriptions","organizations_url":"https://api.github.com/users/bsnyder788/orgs","repos_url":"https://api.github.com/users/bsnyder788/repos","events_url":"https://api.github.com/users/bsnyder788/events{/privacy}","received_events_url":"https://api.github.com/users/bsnyder788/received_events","type":"User","site_admin":false},"labels":[{"id":78928345,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/bug","name":"bug","color":"fc2929","default":true},{"id":560660669,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/high%20priority","name":"high priority","color":"b60205","default":false},{"id":239311849,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/investigation","name":"investigation","color":"eb6420","default":false}],"state":"open","locked":false,"assignee":{"login":"justinj","id":409075,"avatar_url":"https://avatars2.githubusercontent.com/u/409075?v=4","gravatar_id":"","url":"https://api.github.com/users/justinj","html_url":"https://github.com/justinj","followers_url":"https://api.github.com/users/justinj/followers","following_url":"https://api.github.com/users/justinj/following{/other_user}","gists_url":"https://api.github.com/users/justinj/gists{/gist_id}","starred_url":"https://api.github.com/users/justinj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/justinj/subscriptions","organizations_url":"https://api.github.com/users/justinj/orgs","repos_url":"https://api.github.com/users/justinj/repos","events_url":"https://api.github.com/users/justinj/events{/privacy}","received_events_url":"https://api.github.com/users/justinj/received_events","type":"User","site_admin":false},"assignees":[{"login":"justinj","id":409075,"avatar_url":"https://avatars2.githubusercontent.com/u/409075?v=4","gravatar_id":"","url":"https://api.github.com/users/justinj","html_url":"https://github.com/justinj","followers_url":"https://api.github.com/users/justinj/followers","following_url":"https://api.github.com/users/justinj/following{/other_user}","gists_url":"https://api.github.com/users/justinj/gists{/gist_id}","starred_url":"https://api.github.com/users/justinj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/justinj/subscriptions","organizations_url":"https://api.github.com/users/justinj/orgs","repos_url":"https://api.github.com/users/justinj/repos","events_url":"https://api.github.com/users/justinj/events{/privacy}","received_events_url":"https://api.github.com/users/justinj/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/cockroachdb/cockroach/milestones/11","html_url":"https://github.com/cockroachdb/cockroach/milestone/11","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/milestones/11/labels","id":2543483,"number":11,"title":"2.0","description":"","creator":{"login":"dianasaur323","id":5768024,"avatar_url":"https://avatars0.githubusercontent.com/u/5768024?v=4","gravatar_id":"","url":"https://api.github.com/users/dianasaur323","html_url":"https://github.com/dianasaur323","followers_url":"https://api.github.com/users/dianasaur323/followers","following_url":"https://api.github.com/users/dianasaur323/following{/other_user}","gists_url":"https://api.github.com/users/dianasaur323/gists{/gist_id}","starred_url":"https://api.github.com/users/dianasaur323/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dianasaur323/subscriptions","organizations_url":"https://api.github.com/users/dianasaur323/orgs","repos_url":"https://api.github.com/users/dianasaur323/repos","events_url":"https://api.github.com/users/dianasaur323/events{/privacy}","received_events_url":"https://api.github.com/users/dianasaur323/received_events","type":"User","site_admin":false},"open_issues":136,"closed_issues":634,"state":"open","created_at":"2017-05-25T18:08:15Z","updated_at":"2018-03-09T15:23:39Z","due_on":null,"closed_at":null},"comments":21,"created_at":"2018-02-27T19:49:20Z","updated_at":"2018-03-09T15:31:44Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"**BUG REPORT**\r\n\r\n% cockroach version                                                                                                                                                                                                                                                               cockroach\r\nBuild Tag:    v1.1.5\r\nBuild Time:   2018/02/05 17:40:57\r\nDistribution: CCL\r\nPlatform:     linux amd64\r\nGo Version:   go1.8.3\r\nC Compiler:   gcc 6.3.0\r\nBuild SHA-1:  ec52ded95a5235d71f26b6cbc59924bd6733fb3a\r\nBuild Type:   release-gnu\r\n\r\n2. Please describe the issue you observed:\r\n\r\nWe have a strange issue where an index is out of sync and is violating a unique constraint and won't let us easily, manually fix the issue.\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT * FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = false)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n|                        uuid                         |    crm     |     location_id      | active |           end_time           |                                    entity_uuid                                    | initiated_from_ecs |        initiation_time        | message |    operation    | operation_description |                                 org_uuid                                 | parent_task_uuid | progress |          start_time          | status  | synchronized |                       task_id                        | task_type | user_full_name | username |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n| urn:yyy:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657 | 0000 | xxx | false  | 2018-02-25 20:28:40.28+00:00 | xxx:urn:fake:entity:abcd | false              | 2018-02-27 15:19:24.281+00:00 | NULL    | fakeType | fakeType       | xxx:urn:fake:parent:abcd | NULL             |      100 | 2018-02-25 20:05:12.83+00:00 | success | false        | urn:yyy:task:d2e4018b-ebfc-4f26-ac21-cfc713f359c1 | test       | System         | tester    |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n(1 row)\r\n\r\nTime: 128.560204ms\r\n```\r\n\r\nEverything looks fine in that above return.  But if you change synchronized in the query to \"true\", we get:\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT * FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = true)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n|                        uuid                         |    crm     |     location_id      | active |           end_time           |                                    entity_uuid                                    | initiated_from_ecs |        initiation_time        | message |    operation    | operation_description |                                 org_uuid                                 | parent_task_uuid | progress |          start_time          | status  | synchronized |                       task_id                        | task_type | user_full_name | username |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n| urn:yyy:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657 | 0000 | xxx | false  | 2018-02-25 20:28:40.28+00:00 | xxx:urn:fake:entity:abc | false              | 2018-02-27 15:19:24.281+00:00 | NULL    | fakeType | fakeType       | xxx:urn:fake:parent:abcd | NULL             |      100 | 2018-02-25 20:05:12.83+00:00 | success | false        | urn:yyy:task:d2e4018b-ebfc-4f26-ac21-cfc713f359c1 | test       | System         | tester    |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n(1 row)\r\n\r\nTime: 120.803796ms\r\n```\r\n\r\nAs you can see even though we query for synchronized=true directly the resulting data shows that synchronized is still false.\r\n\r\nIf I query it in the following manner the data comes back like so with it being correct in terms of what I queried for:\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT synchronized FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = false)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+--------------+\r\n| synchronized |\r\n+--------------+\r\n| false        |\r\n+--------------+\r\n(1 row)\r\n\r\nTime: 37.420711ms\r\n\r\nroot@cockroach:26257/ecs> SELECT synchronized FROM task@task_by_location_synced WHERE ((location_id = 'xxx') AND (synchronized = true)) AND (initiation_time = '2018-02-27 15:19:24.281+00:00');\r\n+--------------+\r\n| synchronized |\r\n+--------------+\r\n| true         |\r\n+--------------+\r\n(1 row)\r\n\r\nTime: 41.600935ms\r\n```\r\n\r\nIf I query just the base table on the PRIMARY key I get the following:\r\n\r\n```\r\nroot@cockroach:26257/ecs> SELECT * FROM task WHERE \"uuid\" = 'urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657';\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n|                        uuid                         |    crm     |     location_id      | active |           end_time           |                                    entity_uuid                                    | initiated_from_ecs |        initiation_time        | message |    operation    | operation_description |                                 org_uuid                                 | parent_task_uuid | progress |          start_time          | status  | synchronized |                       task_id                        | task_type | user_full_name | username |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n| urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657 | 0000 | xxx | false  | 2018-02-25 20:28:40.28+00:00 | xxx:urn:fake:entity:abc | false              | 2018-02-27 15:19:24.281+00:00 | NULL    | fakeType | fakeType       | xxx:urn:fake:parent:abcd | NULL             |      100 | 2018-02-25 20:05:12.83+00:00 | success | false        | urn:yyy:task:d2e4018b-ebfc-4f26-ac21-cfc713f359c1 | test       | System         | tester    |\r\n+-----------------------------------------------------+------------+----------------------+--------+------------------------------+-----------------------------------------------------------------------------------+--------------------+-------------------------------+---------+-----------------+-----------------------+--------------------------------------------------------------------------+------------------+----------+------------------------------+---------+--------------+------------------------------------------------------+-----------+----------------+----------+\r\n(1 row)\r\n\r\nTime: 134.96595ms\r\n```\r\n\r\nIf I try to manually update the base row to show synchronized = false, I get the following error:\r\n\r\n```\r\nroot@cockroach:26257/ecs> UPDATE task SET synchronized = true WHERE \"uuid\" = 'urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657';\r\npq: duplicate key value (location_id,synchronized,initiation_time)=('urn:xxx:task:2a11a3e9-a071-4624-94a7-5fcbd4fb0657',true,'2018-02-27 15:19:24.281+00:00') violates unique constraint \"task_by_location_synced\"\r\n```\r\n\r\nThus, our row is stuck in a bad state where that field cannot be updated.\r\n\r\nThe schema is as follows:\r\n```\r\n+-----------------------+-----------+-------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n|         Field         |   Type    | Null  | Default |                                                                                               Indices                                                                                                |\r\n+-----------------------+-----------+-------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n| uuid                  | STRING    | false | NULL    | {\"primary\",\"task_by_native_id\",\"task_by_location_synced\",\"task_by_entity_synced\",\"task_by_org_synced\",\"task_by_org\",\"task_by_org_type\",\"task_by_entity\",\"task_by_org_user\",\"continuity_task_by_org\"} |\r\n| crm                   | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| location_id           | STRING    | true  | NULL    | {\"task_by_native_id\",\"task_by_location_synced\"}                                                                                                                                                      |\r\n| active                | BOOL      | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| end_time              | TIMESTAMP | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| entity_uuid           | STRING    | true  | NULL    | {\"task_by_entity_synced\",\"task_by_entity\"}                                                                                                                                                           |\r\n| initiated_from_ecs    | BOOL      | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| initiation_time       | TIMESTAMP | true  | NULL    | {\"task_by_native_id\",\"task_by_location_synced\",\"task_by_entity_synced\",\"task_by_org_synced\",\"task_by_org\",\"task_by_org_type\",\"task_by_entity\",\"task_by_org_user\",\"continuity_task_by_org\"}           |\r\n| message               | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| operation             | STRING    | true  | NULL    | {\"continuity_task_by_org\"}                                                                                                                                                                           |\r\n| operation_description | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| org_uuid              | STRING    | true  | NULL    | {\"task_by_org_synced\",\"task_by_org\",\"task_by_org_type\",\"task_by_org_user\",\"continuity_task_by_org\"}                                                                                                  |\r\n| parent_task_uuid      | STRING    | true  | NULL    | {\"continuity_task_by_org\"}                                                                                                                                                                           |\r\n| progress              | INT       | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| start_time            | TIMESTAMP | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| status                | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| synchronized          | BOOL      | true  | NULL    | {\"task_by_location_synced\",\"task_by_entity_synced\",\"task_by_org_synced\",\"continuity_task_by_org\"}                                                                                                    |\r\n| task_id               | STRING    | true  | NULL    | {\"task_by_native_id\"}                                                                                                                                                                                |\r\n| task_type             | STRING    | true  | NULL    | {\"task_by_org_type\"}                                                                                                                                                                                 |\r\n| user_full_name        | STRING    | true  | NULL    | {}                                                                                                                                                                                                   |\r\n| username              | STRING    | true  | NULL    | {\"task_by_org_user\"}                                                                                                                                                                                 |\r\n+-----------------------+-----------+-------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n\r\nroot@cockroach:26257/ecs> show indexes from task;\r\n+-------+-------------------------+--------+-----+------------------+-----------+---------+----------+\r\n| Table |          Name           | Unique | Seq |      Column      | Direction | Storing | Implicit |\r\n+-------+-------------------------+--------+-----+------------------+-----------+---------+----------+\r\n| task  | primary                 | true   |   1 | uuid             | ASC       | false   | false    |\r\n| task  | task_by_native_id       | false  |   1 | location_id      | ASC       | false   | false    |\r\n| task  | task_by_native_id       | false  |   2 | task_id          | ASC       | false   | false    |\r\n| task  | task_by_native_id       | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_native_id       | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_location_synced | false  |   1 | location_id      | ASC       | false   | false    |\r\n| task  | task_by_location_synced | false  |   2 | synchronized     | ASC       | false   | false    |\r\n| task  | task_by_location_synced | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_location_synced | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_entity_synced   | false  |   1 | entity_uuid      | ASC       | false   | false    |\r\n| task  | task_by_entity_synced   | false  |   2 | synchronized     | ASC       | false   | false    |\r\n| task  | task_by_entity_synced   | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_entity_synced   | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org_synced      | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org_synced      | false  |   2 | synchronized     | ASC       | false   | false    |\r\n| task  | task_by_org_synced      | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org_synced      | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org             | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org             | false  |   2 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org             | false  |   3 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org_type        | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org_type        | false  |   2 | task_type        | ASC       | false   | false    |\r\n| task  | task_by_org_type        | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org_type        | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_entity          | false  |   1 | entity_uuid      | ASC       | false   | false    |\r\n| task  | task_by_entity          | false  |   2 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_entity          | false  |   3 | uuid             | ASC       | false   | true     |\r\n| task  | task_by_org_user        | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | task_by_org_user        | false  |   2 | username         | ASC       | false   | false    |\r\n| task  | task_by_org_user        | false  |   3 | initiation_time  | DESC      | false   | false    |\r\n| task  | task_by_org_user        | false  |   4 | uuid             | ASC       | false   | true     |\r\n| task  | continuity_task_by_org  | false  |   1 | org_uuid         | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   2 | parent_task_uuid | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   3 | synchronized     | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   4 | operation        | ASC       | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   5 | initiation_time  | DESC      | false   | false    |\r\n| task  | continuity_task_by_org  | false  |   6 | uuid             | ASC       | false   | true     |\r\n+-------+-------------------------+--------+-----+------------------+-----------+---------+----------+\r\n(37 rows)\r\n```\r\n\r\nLet me know if there is anything else I can provide to help get to the bottom of this issue. I am willing to screen share if necessary, etc.\r\n\r\nThanks!\r\n"},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/371845962","html_url":"https://github.com/cockroachdb/cockroach/issues/23176#issuecomment-371845962","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/23176","id":371845962,"user":{"login":"bdarnell","id":160562,"avatar_url":"https://avatars1.githubusercontent.com/u/160562?v=4","gravatar_id":"","url":"https://api.github.com/users/bdarnell","html_url":"https://github.com/bdarnell","followers_url":"https://api.github.com/users/bdarnell/followers","following_url":"https://api.github.com/users/bdarnell/following{/other_user}","gists_url":"https://api.github.com/users/bdarnell/gists{/gist_id}","starred_url":"https://api.github.com/users/bdarnell/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bdarnell/subscriptions","organizations_url":"https://api.github.com/users/bdarnell/orgs","repos_url":"https://api.github.com/users/bdarnell/repos","events_url":"https://api.github.com/users/bdarnell/events{/privacy}","received_events_url":"https://api.github.com/users/bdarnell/received_events","type":"User","site_admin":false},"created_at":"2018-03-09T15:31:44Z","updated_at":"2018-03-09T15:31:44Z","author_association":"OWNER","body":"OK. I haven't been able to reproduce this but I *think* I understand what's going on. \r\n\r\ntl;dr: This is a bug in our \"one phase commit\" optimization for transactions using SNAPSHOT isolation, leading to lost update anomalies. \r\n\r\nLet's simplify the schema for discussion:\r\n\r\n```\r\nCREATE TABLE t (a INT PRIMARY KEY, b INT, c INT, INDEX (b, c))\r\n```\r\n\r\nWe have multiple clients doing `UPSERT INTO t VALUES ($1, $2, $3) RETURNING NOTHING`. In a table with no indexes, this would be a write-only operation: just blindly write the three values into the primary row for the table. Since there is an index, we must *replace* any index entries that previously existed with our own. This is implemented as a read followed by a write. We read the current value for `$1`, and if a value is found we add a deletion for the index record `($2, $3)` to our write set.  Then we add our new values for both the primary and secondary indexes to the write set and submit it all as one batch to the KV layer.  If another write occurs in between the read and the write, this would invalidate our assumptions: the index records we delete do not match the current values, so we may not delete the right ones.\r\n\r\nOne way to think about this is that while SERIALIZABLE transactions operate at a point in time, SNAPSHOT transactions operate on a *range* of time from their original read timestamp to the write timestamp determined when they commit. The implementation of this is somewhat subtle (and i had to re-learn it as a part of this investigation). Even when a SNAPSHOT transaction's timestamp is \"pushed\", this doesn't take effect until COMMIT time. The \"active\" timestamp of a batch is always the transaction's original timestamp:\r\n\r\nhttps://github.com/cockroachdb/cockroach/blob/35a94fc0e04819deb6d737ff2ac8b3dc91218764/pkg/roachpb/batch.go#L42-L45\r\n\r\nThis means that our write intents effectively cover the entire span between the transaction's original timestamp and its final pushed timestamp. If your transaction's original timestamp is not greater than the latest write, you have to restart, even if your pushed timestamp is higher. \r\n\r\nBut things get tricky with our \"one phase commit\" optimization. Under certain conditions, we [discard](https://github.com/cockroachdb/cockroach/blob/35a94fc0e04819deb6d737ff2ac8b3dc91218764/pkg/storage/replica.go#L5157) the transaction's original timestamp, and run the write batch with only a reference to the final pushed timestamp. This collapses the snapshot transaction's timestamp range into a single point in time. In this case, we could leapfrog earlier committed values without getting a WriteTooOldError. This would cause inconsistencies in our index updates.\r\n\r\nThis is further complicated by the fact that the one phase commit optimization only operates under certain circumstances. Both UPSERT and RETURNING NOTHING are required (based on my current analysis) to get into this state, and it requires a race that is difficult to reproduce in isolation. \r\n\r\nThe one-phase commit optimization is currently more likely to trigger for SNAPSHOT transactions than SERIALIZABLE ones (we disallow the optimization if the timestamp has been pushed for SERIALIZABLE, but not for SNAPSHOT); I believe we can fix this by disallowing the optimization for pushed transactions regardless of their isolation. \r\n"}}
{"actor":{"display_login":"bdarnell"}}