3,674 reputation
513
bio website
location Vancouver, Canada
age
visits member for 3 years, 4 months
seen 4 hours ago

6h
comment How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?
I'm sure when statement gets executed only one row is locked (LIMIT 1). However, during execution of the statement the engine may put locks on all records that satisfy WHERE (note, order on which rows are actually locked is never guaranteed). I'm not postgres guy, so I can't state this is what happens in your case, but it would explain deadlocks.
2d
awarded  Popular Question
Nov
13
answered Constrain Partial Values in Timestamp
Nov
13
comment Slow execution of a subquery with 0 matches
I think the main reason is ORDER BY authorID in subquery (ignoring the fact that order by is useless in this case as RolandoMySQLDBA pointed out) . Note: there is no Followers.authorId so it's resolved to Posts. authorId
Nov
6
comment Failed to drop column - value too large
Is it any chance you have ddl triggers on database or schema level ?
Nov
5
comment Truncating and inserting in a safe transaction on MySQL
When does it say that DELETE causes implicit commit ?
Oct
16
comment Duplicate records on joining
@Dima Knivets: Can you add an example that illustrates the problem ? For instance, using SQLFiddle, so everyone can see exactly what kind of duplicates you are getting .
Oct
10
comment Duplicate records on joining
@ Dima Knivets : you have DISTINCT keyword, so book.id+date are unique; order by unique value is consistent - in my example tuple "id=2, some_non_unique_data = 3" is always 'before' "id=2, some_non_unique_data = 3" (surely when id included in order by clause)
Oct
10
revised Duplicate records on joining
added 30 characters in body
Oct
10
comment Duplicate records on joining
@Dima Knivets: I added more descriptive example , hope that helps.
Oct
10
revised Duplicate records on joining
added 263 characters in body
Oct
10
answered Duplicate records on joining
Oct
10
answered Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?
Oct
9
comment Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?
As far as I remember there is an attribute sql%ROWCOUNT, not sql%rownum...
Oct
7
comment Does trigger “see” all changes done in the transaction it was fired from?
Check docs.oracle.com/cd/B19306_01/appdev.102/b14261/…
Oct
7
answered Does trigger “see” all changes done in the transaction it was fired from?
Oct
7
comment Does trigger “see” all changes done in the transaction it was fired from?
Yes, unless the trigger created with PRAGMA AUTONOMOUS_TRANSACTION
Oct
3
answered SQL join with multiple columns as FK to same list table
Oct
3
comment Updating a table with a single UPDATE statement vs several UPDATE statements
Then I don't see a big difference ; maybe 4 "small" updates are a bit better from readability perspective.
Oct
3
answered Updating a table with a single UPDATE statement vs several UPDATE statements