PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Locking objects in SQL Source Control

The beta of SQL Lock has now been retired and Redgate have rolled the object locking feature into the SSMS plug-in SQL Source Control. Read more about object locking in SQL Source Control here.

If you share a database with other developers, chances are you’ve encountered a situation like this:

1. Alice starts editing an object.

2. Meanwhile, not realizing Alice is working on the object, Bob unwittingly edits and saves the same object.

3. Alice, not realizing Bob was working on the same thing, saves her changes.

Presto: Bob’s work is gone. And what’s more, Bob and Alice might not even notice. The error could make it all the way to production. We spoke to one developer who lost a week’s work when their database was deployed without critical changes that had been accidentally overwritten.

We have some experience solving this problem. In 2013, we released Source Control for Oracle 2, with a feature to let users lock objects on Oracle databases so other people can’t edit them. After that, we released a free beta of SQL Lock, an add-in for Management Studio that does the same job for SQL Server. Now we’re retiring SQL Lock and rolling the feature into SQL Source Control.

The feature is simple. When you want to work on an object, you right-click it in the Object Explorer and select Lock this object.

lock-this-object.png

After you lock it, the object is marked with a padlock icon in the Object Explorer. If other people try to save changes to the object, they’ll see an error explaining that it’s locked by you.

Of course, if you take a vacation or get hit by a bus, your team’s going to need to work on your locked objects without you. For that reason, we let anyone unlock objects, even if they didn’t lock it themselves. We see the feature not as a way to protect your changes but to stop people stepping on your toes.

Locking-tab1.png

All the locking is done on the server, so it works independently of your source control system.

You can try object locking out by installing SQL Source Control 4 on Frequent Updates. When SQL Source Control 4 is released on our main release channel, we’ll remove the feature from SQL Source Control 3 – so try it now, while it’s hot!