Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: subject SELECT FOR UPDATE to sql_safe_updates #110131

Closed
nvanbenschoten opened this issue Sep 6, 2023 · 0 comments · Fixed by #121403
Closed

sql: subject SELECT FOR UPDATE to sql_safe_updates #110131

nvanbenschoten opened this issue Sep 6, 2023 · 0 comments · Fixed by #121403
Assignees
@michae2
Labels
A-read-committed Related to the introduction of Read Committed A-sql-optimizer SQL logical planning and optimizations. branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker T-sql-queries SQL Queries Team

Comments

@nvanbenschoten
Copy link
Member

nvanbenschoten commented Sep 6, 2023 •
edited by cockroach-jira-scripts

SELECT FOR UPDATE will acquire a lock on each row returned. For serializable isolation transactions, these locks are unreplicated (today). For weak isolation transactions, these locks are replicated and will cause disk writes.

This makes a full-table locking scan fairly dangerous. To avoid cluster instability, we should subject full-table locking scans to sql_safe_updates, so that users either need to SET sql_safe_updates = false or add a WHERE true predicate to run them.

Jira issue: CRDB-31277
@nvanbenschoten nvanbenschoten added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-optimizer SQL logical planning and optimizations. T-sql-queries SQL Queries Team A-read-committed Related to the introduction of Read Committed labels Sep 6, 2023
@michae2 michae2 self-assigned this Sep 6, 2023
@michae2 michae2 mentioned this issue Mar 30, 2024
Merged
craig bot pushed a commit that referenced this issue Apr 1, 2024
@michae2
Merge #121403
55a5b64 
121403: optbuilder: subject SELECT FOR UPDATE to sql_safe_updates r=nvanbenschoten,rytaft a=michae2

Fixes: #110131

Release note (sql change): With sql_safe_updates set to true, SELECT FOR UPDATE and SELECT FOR SHARE statements now return an error if they do not contain either a WHERE clause or LIMIT clause.

Also, UPDATE and DELETE statements without WHERE clauses but with LIMIT clauses now bypass sql_safe_updates, which better matches MySQL behavior.

Co-authored-by: Michael Erickson <michae2@cockroachlabs.com>
@craig craig bot closed this as completed in af31225 Apr 1, 2024
@blathers-crl blathers-crl bot mentioned this issue Apr 1, 2024
Merged
@michae2 michae2 added GA-blocker branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 labels Apr 1, 2024
michae2 added a commit that referenced this issue Apr 3, 2024
@michae2
optbuilder: subject SELECT FOR UPDATE to sql_safe_updates
0278ec4 
Fixes: #110131

Release note (sql change): With sql_safe_updates set to true, SELECT FOR UPDATE and
SELECT FOR SHARE statements now return an error if they do not contain
either a WHERE clause or LIMIT clause.

Also, UPDATE and DELETE statements without WHERE clauses but with LIMIT
clauses now bypass sql_safe_updates, which better matches MySQL
behavior.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@michae2 michae2

Labels
A-read-committed Related to the introduction of Read Committed A-sql-optimizer SQL logical planning and optimizations. branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker T-sql-queries SQL Queries Team
Projects
SQL Queries
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

optbuilder: subject SELECT FOR UPDATE to sql_safe_updates michae2/cockroach
2 participants
@nvanbenschoten @michae2