3 ms·

I think these are great. But I think there should be some representation around locking / concurrency / deadlock topics. Those tend to be the hardest because yo

I think these are great. But I think there should be some representation around locking / concurrency / deadlock topics. Those tend to be the hardest because you can’t clearly recreate the right/wrong answer in a local test environment. Speaking as a person who waited far too long in his career to fully appreciate these topics, I wish I had been pushed to learn them much earlier.

jandrewrogers 6y ago
Behaviors around locking and concurrency are not standard. Not only do they vary widely across RDBMS implementations, they also vary depending on how you configure a specific RDBMS implementation in many cases. Some databases use lock structures that automatically detect and resolve deadlocks, so from a user standpoint there are no deadlocks but deadlock resolution has visible side effects that are implementation defined.
- hobs 6y ago
  Yeah but the functions and style used in these answers vary across implementations (date_trunc, etc.) I highly recommend the SQL Cookbook for newer SQL dev as a quick reference comparison to see how often this is the case for even trivial problems in any RDBMS. Also, for your language I wouldn't expect all mid tier SQL devs to be able to write a recursive CTE from memory (though its useful, you can just look it up again), but something like breaking apart a query plan on your platform of choice is way more important as that muscle allows you to tell if your CTE was crap or not.
bigtechdataeng 6y ago
I suspect more SQL is written to support analytics than online systems. Locking and concurrency are used in a specific type of application, namely oltp. I learned how much when I joined a big tech company. The devs don’t write sql unless processing logs in the warehouse. But everyone from PM to support to data science and marketing all write sql.
- deleted 6y ago
  [deleted]
- thomzi12 6y ago
  To your point, I personally write SQL for analytics and product/business purposes, not system monitoring

jandrewrogers 6y ago

Behaviors around locking and concurrency are not standard. Not only do they vary widely across RDBMS implementations, they also vary depending on how you configure a specific RDBMS implementation in many cases. Some databases use lock structures that automatically detect and resolve deadlocks, so from a user standpoint there are no deadlocks but deadlock resolution has visible side effects that are implementation defined.

hobs 6y ago
Yeah but the functions and style used in these answers vary across implementations (date_trunc, etc.) I highly recommend the SQL Cookbook for newer SQL dev as a quick reference comparison to see how often this is the case for even trivial problems in any RDBMS. Also, for your language I wouldn't expect all mid tier SQL devs to be able to write a recursive CTE from memory (though its useful, you can just look it up again), but something like breaking apart a query plan on your platform of choice is way more important as that muscle allows you to tell if your CTE was crap or not.

bigtechdataeng 6y ago

I suspect more SQL is written to support analytics than online systems. Locking and concurrency are used in a specific type of application, namely oltp. I learned how much when I joined a big tech company. The devs don’t write sql unless processing logs in the warehouse. But everyone from PM to support to data science and marketing all write sql.

deleted 6y ago
[deleted]
thomzi12 6y ago
To your point, I personally write SQL for analytics and product/business purposes, not system monitoring