Over the last few months, we’ve been visiting customers and helping them get up and running with Red Gate tools. Something we’ve seen over and over is that teams have to avoid stepping on each other’s toes when sharing a database, because there’s a constant risk of accidentally overwriting changes.

A typical scenario goes something like this:

1. Alice opens a package and starts editing it.
2. Meanwhile, Bob unwittingly opens the same package, edits it, and saves it.
3. Alice saves her changes to the package.

Presto: Bob’s work is gone. And what’s more, Bob and Alice may 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.

There are ways to undo this kind of mistake. Some types of objects can be rescued from the recycle bin, or recovered with backups or source control; if you have Oracle Flashback Technology enabled, you can return PL/SQL objects to a previous state. None of these methods are comprehensive or reliable, they can be time-consuming, and they depend on the problem being noticed at all.

It’s better to prevent the mistake happening in the first place. To that end, some developers rely on email, IM, or text messages to warn their team when they’re working on something. Others leave comments in the code or use Post-it notes. We’ve even seen people shout across the office. In every case, the solution wasn’t reliable: Alice had to remember to warn Bob she was working on something, and Bob had to take notice. Some teams have a “play nice” policy, and plan projects so people don’t end up working on things simultaneously, but that isn’t ideal, either. It’s basically a communication problem.

With Source Control for Oracle 2, you can lock database objects before you start work on them and leave a comment explaining what you’re doing. Then you can carry on working normally without worrying about someone else overwriting your work. If someone goes to edit the locked object, the server returns an error with your comment, so they know what’s going on.

You can set up the Red Gate schema from the Lock objects tab in Source Control for Oracle. The tool runs a script to create a Red Gate schema on your database; it’s all transparent, so you can review the script and see exactly what’s going on. Afterwards, the tab shows which objects are currently locked (by you or other people). You can see who locked the object, when they locked it, and how recently it was edited. The locking functionality works for every project connected to the database.

When someone locks an object, information about who locked it is stored in the locked_objects table along with their comment. When anyone executes some DDL, a system-level trigger checks they have permission to edit the referenced object. If that person locked the object themselves, they can edit it as normal. Everyone else will see an error, even if they’re signed in as the same Oracle user. The error contains the comment left by the person who locked it.

It’s not totally draconian. You can still unlock other people’s objects if they’re off sick or on vacation or taking an extended lunch break, or if you’re just feeling mischievous. We see the locking feature as a communication tool to replace the emails and Post-its. The difference is that it’s easy to communicate, and impossible to miss.