Voice of the DBA

Author: way0utwest

Concurrency Challenges Around Schema Changes
I saw a great question on Twitter from Frank Pachot, a developer advocate of Yugabyte. He wrote: Without thinking how your preferred database deals with it, what do you expect if:
- session 1 starts to reads table T
- session 2 drops table T
- session 1 continues to read
The choices in his poll were: session 2 waits, session 2 fails, session 1 fails, both fail. My first thought was SQL Server and the default need for session 2 to get an exclusive lock. In that case, session 2 would wait. Most people answered that same way, but then Frank posted a follow-up with a link to his blog. The answer for Yugabyte is that session 1 fails as it gets the message that the table was deleted.

Leaving aside the decision to drop a table, imagine this is some schema change instead. In the blog, some good points are raised about how to handle high concurrency changes, and the potential problems with having session 2 wait. On a busy system, this could cause lots of blocking as threads stack up behind session 2.

It’s an interesting read about the challenges of distributed system design and how to handle changes. In some sense, I get that this makes sense, but I wonder where this causes issues. If any schema change on the table by session 2 were to cause an error in session 1, that would be bad. However, does this mean that the database engine must now evaluate whether a column change impacts a query in flight? Then decide to send an error? What about evaluating views or procedures/functions that depend on

Does this mean that all nodes need to sync up the schema changes quickly, and at a higher priority than data movements? I don’t know exactly how Yugabyte distributes data, and if there are copies on multiple nodes, but I assume there are. This adds complexity to the communication between nodes, which is likely needed. Honestly, if someone drops a table and they should have, we probably don’t want clients getting results. If they do this accidentally, I’d like to know about it quickly.

The question is interesting, and there are multiple ways to look at this, but I found it fascinating to spend a few minutes thinking about the complexities of data in distributed systems and the challenges involved. This also made me think that the people who keep data safe and fix problems when they occur are invaluable in the modern world.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
June 26, 2023
The Best Career Advice

I don’t know that I have the best advice, but this month’s T-SQL Tuesday is asking for people to share what they think is the best advice they’ve been given or have for you. I wrote my own piece, where I noted that learning to say “No” was one of the best things I’ve ever done. Not that I say no to everything, but I do default to no, especially when someone asks for me to tackle something new.

Actually, it’s slightly more nuanced than that. As I’ve gotten used to my workload, I will say yes to things, and certainly, I’m more likely to commit to one-off things. It’s the longer-term, larger things that I don’t want to agree to do unless I’m sure I can deliver.

There are lots of other things people wrote. Deb said that you should trust your instincts and realize you can contribute, even if you’re new. Hugo notes the user is often right. Mala is more cautious with work and practices discretion. Rob got the advice to take it slow, learn his job, and figure out what he likes and doesn’t.

There are lots of other advice, from Pragati telling you to get a mentor to Mikey saying you should find a job you love. If you check out the comments in the invitation above, you’ll see plenty more responses, many with interesting back stories and more details. If you only read through one set of T-SQL Tuesday responses, this might be the one to pick.

I’m a big fan of actively managing your career. Make the decisions that move in the direction that matters to you. As noted in a few posts, we spend a lot of our lives at work. At times more than we spend with family, so be sure you have a career you enjoy.

This takes work, but it’s an investment that can repay itself over many years. Both in financial rewards and less stress on a regular basis. Every job is a job some days, but when you enjoy your work, it doesn’t feel like work.

Steve Jones

June 24, 2023
A New Word: Volander

volander: n. the ethereal feeling of looking down at the world through an airplane window, able to catch a glimpse of far-flung places you’d never see in person, free to let your mind wander, trying to imagine what they must feel like down on the ground – the closest you’ll ever get to an objective point of view.

I snap these types of photos all the time. This was me leaving Denver for Fort Lauderdale recently:

Here was last year above Hawaii:

and leaving Melbourne:

As I fly, I sometimes think about the world below me, what it’s like from the ground, what it’s like for people who’ve walked there. Sometimes I plan to go someday and see.

Often, I know I’ll never get there.

I flew to Las Vegas a few weeks ago, and there are lots of places I could visit that I see, and some I have. However, much of the mountains above Colorado, the desert outside Las Vegas to the East, these are places I’ll never go.

But I can dream.

From the Dictionary of Obscure Sorrows

June 23, 2023
Agile West–How Do I Get Started with Database DevOps

This week I had the chance to deliver a talk at Agile West in Las Vegas. I linked some resources on my blog, and feel free to check them out.

Tl;Dr – Start with deployments

After the talk, I had an interesting question from someone in the audience. This person had a lot of developers writing code and then sending to a DBA, who executes it in SSMS. This was for SQL Server development, and that’s a common way many companies deploy database changes.

Even companies that have software developers who embrace DevOps will still tend to work this way.

This is error prone, inefficient, and it’s not the way to build better software. We know that DevOps produces better software if you adopt it, and we know that you can’t forget the database. That’s integral if you want to be a high or elite performer.

My advice for many companies is that they start with deployments. If you have some manual process or DBAs involved, often they’re just running a script you produced in some way. What I’d do is start adding Flyway Community (or Teams/Enterprise) in and putting those scripts into a folder (hopefully in Git) and naming them as appropriate. That’s a small change for DBAs or Ops people deploying code, but it starts to enable a process.

From here, I can alter this process to use tooling (Azure DevOps, Bamboo, Jenkins, etc.) and continue to put scripts in a location. Then I can work backwards and start getting developers to build better processes for capturing and saving code.

June 21, 2023