What is a Commit in SQL?

by

If you’ve spent any time working with databases, you’ve probably noticed that most of your SQL statements just work. You run an INSERT, UPDATE, or DELETE, and the changes happen. You don’t need to do anything special to make them stick. Your changes were committed automatically as soon as you ran the statement. No need for a separate COMMIT keyword.

But then there are other cases where you need to explicitly use a COMMIT keyword.

So why is COMMIT required in some cases and not in others?

Read more

What is Transaction Starvation?

by

Transaction starvation is one of those database problems that can sneak up on you when you least expect it. It happens when a transaction sits waiting for resources it needs to complete, but those resources never become available, or at least not for an unreasonably long time. The transaction essentially “starves” while other transactions keep getting priority access to the resources it needs.

Read more

What is a Deadlock Victim?

by

Anyone who manages a high-traffic database is almost certainly familiar with the dreaded deadlock error. When this circular dependency freezes concurrent operations, your database management system must intervene by selecting a deadlock victim – one transaction it immediately terminates and rolls back.

But what exactly is a “deadlock victim”, and why does your database seem to be picking on certain transactions? Let’s take a look, and explore why databases make these tough decisions.

Read more

What is Pessimistic Concurrency Control?

by

When multiple users or processes of a database are trying to access and modify the same data at the same time, things can get messy pretty quickly. That’s where concurrency control comes in. This the set of strategies databases use to make sure everyone plays nicely together. One of the classic strategies for managing this is called pessimistic concurrency control. The name might sound gloomy, but it’s actually a very practical approach to keeping your data consistent and reliable.

Read more

Displaying Abbreviated and Full Day Names for Reports in SQL Server

by

When building reports in SQL Server, dates are probably one of the most common pieces of data you’ll deal with. Sometimes a report needs the full day name like “Monday”, while in other cases a short form like “Mon” is preferred, often to save space. Luckily, SQL Server has built-in functionality to handle both, without having to manually map numbers to names.

Let’s look at how we can display abbreviated and full day names in queries so that our reports are nice and easy to read.

Read more

What is Optimistic Concurrency Control?

by

Imagine you’re working on a shared Google Doc with a colleague. You both open the same document, make your edits, and hit save. Now imagine if every time someone wanted to edit the document, they had to lock it so nobody else could even read it while they were making changes. That would be pretty frustrating, right? This is essentially the problem that optimistic concurrency control tries to solve in databases.

Read more

What is False Contention in a Database?

by

Imagine you’re at a coffee shop waiting in line to be served, but the line isn’t moving. And then you realize that the person in front of you isn’t even waiting to order. They’re just standing there doing nothing. And now they’ve forced you to stand there and do nothing. That’s basically what false contention looks like in a database.

Read more

What is Database Contention?

by

Database contention is one of those problems that can sneak up on you when your application starts getting real traffic. It’s what happens when multiple processes or transactions try to access the same database resources at the same time, and they end up getting in each other’s way.

When your application is small and you’ve got just a handful of users, database contention rarely matters. But as you scale up and start handling hundreds or thousands of concurrent requests, suddenly you’ve got queries waiting in line, locks piling up, and performance grinding to a halt. Contention is an important consideration for anyone building or maintaining applications that need to perform well under load.

Read more

Using “GO” to Structure T-SQL Batches

by

If you spend much time writing T-SQL scripts, you’ve probably seen the GO keyword. It looks like a T-SQL command, but it’s not really part of T-SQL. Instead, it’s a batch separator recognized by SQL Server Management Studio (SSMS) and other client tools. When you hit the “Execute” button, any GO keyword in your script tells the tool that this is the end of a batch, and to send what came before it to SQL Server as one unit.

Many scripts will run fine without the GO keyword, but others will fail miserably. Understanding how GO works can save you from frustrating errors and unexpected behavior.

Read more