{"id":8269,"date":"2015-09-03T12:19:16","date_gmt":"2015-09-03T12:19:16","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/locking-objects-in-sql-server\/"},"modified":"2016-07-28T10:56:56","modified_gmt":"2016-07-28T10:56:56","slug":"locking-objects-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/locking-objects-in-sql-server\/","title":{"rendered":"Locking objects in SQL Server"},"content":{"rendered":"

As my colleague James said in a recent blog post<\/a>:<\/p>\n

“If you share a database with other developers, chances are you’ve encountered a situation like this:<\/p>\n

1. Alice starts editing an object.<\/p>\n

2. Meanwhile, not realizing Alice is working on the object, Bob unwittingly edits and saves the same object.<\/p>\n

3. Alice, not realizing Bob was working on the same thing, saves her changes.<\/p>\n

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.”<\/p>\n

It’s a familiar story. Two developers are working on a database, they’re using version control, and one of them unwittingly overwrites the others’ changes. I’ve heard first how frustrating this can be – we spoke to one developer who lost a week’s work when their database was deployed without critical changes that had been accidentally overwritten. Ouch.<\/p>\n

A bit of background:<\/strong> In 2013, we released Source Control for Oracle 2, which included a feature to let users lock objects on Oracle databases so that other users can’t edit them. A while later, we released a free beta of SQL Lock<\/a>, an add-in for Management Studio, to try and solve the same problem for SQL Server development teams. We’ve recently retired the SQL Lock beta and have rolled the feature into SQL Source Control<\/a>. In case you’re not familiar with the latter, SQL Source Control is an add-in for SQL Server Management Studio that links your database to your version control system.<\/p>\n

So, how do you use object locking? Simple. Once you’ve set it up and you’re ready to work on an object, just right-click it in the Object Explorer and select Lock this object<\/strong>.<\/p>\n

\"lock-this-object.png\"<\/a><\/p>\n

When you’ve locked it, the object is marked with a padlock icon in the Object Explorer.<\/p>\n

\"Capture1.jpg\"<\/a><\/p>\n

If someone else tries to save changes to the object, they’ll see an error explaining that it’s locked by you.<\/p>\n

Of course, if you take a week off work or get hit by a bus, your team’s going to need to work on your locked objects without you. For that reason, anyone can unlock an object, even if they didn’t lock it themselves. This way the team can continue working on the object without any delays. We see the feature as not being a way to enforce rules, but something to help you to not tread on each other’s toes.<\/p>\n

If you go to the Object locking tab you can see all of the objects which have been locked on that server – select the ones which you’d like to unlock and click Unlock selected objects<\/strong>.<\/p>\n

\"Locking-tab-2.jpg\"<\/a><\/p>\n

All the locking is done on the server, so it works independently of your version control system.<\/p>\n

If you’re more of a video person than a blog person then you can watch this walk-through video of the locking feature by Microsoft MVP Alessandro Alpi:<\/p>\n<\/p>\n

If you’d like to try object locking out you’ll need to install the latest version of SQL Source Control, which has just been released – hurrah!. Download it here<\/a> and feel comforted that your work is in safe hands. Oh, and let me know what you think – if you have any feedback I’d love to hear it.<\/p>\n

What’s next for SQL Source Control? <\/strong>Over the next 6 months you’ll see a lot of changes and improvements to SQL Source Control. We’ll be posting here soon about what’s coming up.<\/p>\n","protected":false},"excerpt":{"rendered":"

As my colleague James said in a recent blog post: “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……<\/p>\n","protected":false},"author":8857,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-8269","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/8857"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8269"}],"version-history":[{"count":30,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8269\/revisions"}],"predecessor-version":[{"id":42511,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8269\/revisions\/42511"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8269"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}