{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/2859","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/2859/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/2859/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/2859/events","html_url":"https://github.com/cockroachdb/cockroach/issues/2859","id":112175080,"number":2859,"title":"kv: reduce surprising serializable transaction restarts","user":{"login":"petermattis","id":6201335,"avatar_url":"https://avatars.githubusercontent.com/u/6201335?v=3","gravatar_id":"","url":"https://api.github.com/users/petermattis","html_url":"https://github.com/petermattis","followers_url":"https://api.github.com/users/petermattis/followers","following_url":"https://api.github.com/users/petermattis/following{/other_user}","gists_url":"https://api.github.com/users/petermattis/gists{/gist_id}","starred_url":"https://api.github.com/users/petermattis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/petermattis/subscriptions","organizations_url":"https://api.github.com/users/petermattis/orgs","repos_url":"https://api.github.com/users/petermattis/repos","events_url":"https://api.github.com/users/petermattis/events{/privacy}","received_events_url":"https://api.github.com/users/petermattis/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":7,"created_at":"2015-10-19T15:56:17Z","updated_at":"2015-10-19T20:54:04Z","closed_at":null,"body":"Consider the following. In the `./cockroach sql` shell do:\r\n\r\n```\r\ncreate database d1;\r\ncreate table d1.t1 (col1 int primary key);\r\ninsert into d1.t1 values (1);\r\nbegin;\r\ninsert into d1.t1 values (2);\r\n```\r\n\r\nNotice the transaction has not committed. Now, in a separate shell do:\r\n\r\n```\r\nselect * from d1.t1;\r\n```\r\n\r\nFinally, back in the first shell:\r\n\r\n```\r\ncommit;\r\n```\r\n\r\nYou should get the error `query error: kv/txn_coord_sender.go:375: transaction is already committed or aborted`. \r\n\r\nThis is surprising. Why did a read-only operation cause the transaction to abort? Seems that the proximate cause is that the scan operation caused resolution of the write intents which pushed the transaction timestamp. @spencerkimball suggested we could allow the transaction timestamp for a write-only transaction to be pushed without restarting the transaction. \r\n\r\nPostgres seems to take an even finer-grained approach and tracks when a write could have had an impact on the result of a previous read from a concurrent transaction had it run first. For example:\r\n\r\n```\r\ncreate table t1 (col1 bigint primary key);\r\ninsert into t1 values (1);\r\n\r\nbegin transaction isolation level serializable;\r\ninsert into t1 (select col1+10 from t1 where col1=1);\r\ncommit;\r\n\r\nbegin transaction isolation level serializable;\r\ninsert into t1 (select col1+100 from t1 where col1=1);\r\ncommit;\r\n```\r\n\r\nEven if the two transactions are run concurrently, the transactions do not conflict. In Cockroach, one of the transactions will be aborted. I'm not sure if this latter case is worth worrying about, but the former case of a read-only transaction causing a write-only transaction to abort certainly seems worth addressing."},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/149344073","html_url":"https://github.com/cockroachdb/cockroach/issues/2859#issuecomment-149344073","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/2859","id":149344073,"user":{"login":"tschottdorf","id":5076964,"avatar_url":"https://avatars.githubusercontent.com/u/5076964?v=3","gravatar_id":"","url":"https://api.github.com/users/tschottdorf","html_url":"https://github.com/tschottdorf","followers_url":"https://api.github.com/users/tschottdorf/followers","following_url":"https://api.github.com/users/tschottdorf/following{/other_user}","gists_url":"https://api.github.com/users/tschottdorf/gists{/gist_id}","starred_url":"https://api.github.com/users/tschottdorf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tschottdorf/subscriptions","organizations_url":"https://api.github.com/users/tschottdorf/orgs","repos_url":"https://api.github.com/users/tschottdorf/repos","events_url":"https://api.github.com/users/tschottdorf/events{/privacy}","received_events_url":"https://api.github.com/users/tschottdorf/received_events","type":"User","site_admin":false},"created_at":"2015-10-19T20:54:04Z","updated_at":"2015-10-19T20:54:04Z","body":"> I might be reading the code wrong, but it looks like we only allow pushing the timestamp of SNAPSHOT isolation transactions.\r\n\r\nYes, that's exactly the difference between `SNAPSHOT` and `SERIALIZABLE`. Checking on commit actually like a good idea and wouldn't be hard to do. We could extend the `Writing` flag to have the possible states `R|W|RW` and a serializable `W` transaction would be able to commit with a pushed timestamp.\r\n\r\n> Seems difficult for the command line SQL shell to retry transactions correctly. How do you redo the logic that the user has in their head? I must be missing something about your proposal.\r\n\r\nI don't think that's going to be too pretty. The user would essentially have to \"program\" in SQL like a KV retryable would (so no ad-hoc numbers or strings). I don't have anything in my head that I would dare call a \"proposal\"."}}