If you’ve ever wondered why your database queries sometimes seem to wait around doing nothing, or why two users can’t update the same record at the exact same moment, you’re dealing with locks. In SQL Server, locks are the fundamental mechanism that keeps your data consistent and prevents the chaos that would ensue if everyone could modify everything simultaneously.
transactions
What is a Savepoint in SQL?
When working with databases, there’s a good chance you’ve had to deal with transactions. Transactions are those “all or nothing” blocks of work that make sure your data stays consistent. But what happens if you’re halfway through a transaction and realize that only part of it needs to be undone, not the whole thing? That’s where savepoints can help.
In SQL, a savepoint is basically a checkpoint you can set inside a transaction. It lets you roll back to that specific point if something goes wrong, without undoing everything that came before it. If something gets messed up, you can load your last save instead of starting again from scratch.
What is a Checkpoint in a Database?
If you’ve ever wondered how databases manage to recover your data after a crash without losing everything you just did, checkpoints are a big part of that. They’re one of those behind-the-scenes mechanisms that keep databases running smoothly, even when things go wrong.
What is a Rollback in SQL?
In SQL, a rollback is a command that reverses all the changes made during a transaction. When you execute a ROLLBACK statement, the database management system undoes all the Data Manipulation Language (DML) operations (such as INSERT, UPDATE, and DELETE) that happened since the transaction began (or since a specified savepoint), restoring the database to its previous consistent state.
What is a Commit in SQL?
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?
What is Transaction Starvation?
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.
What is a Deadlock Victim?
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.
What is Pessimistic Concurrency Control?
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.
What is Optimistic Concurrency Control?
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.
What is False Contention in a Database?
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.
