Database Related

What Is A Database Lock In SQL?

Imagine that we are working on a database. Our database just happens to catalog all of our favorite movies. We are the only person that use our database. That is until our best friend Tommy wants to help. Tommy really likes movies too and thinks this is a cool system. Tommy is an awesome dude but doesn’t always follow directions. We tell Tommy that we are going to work on the movie descriptions and he should stay out of them. Later in the day though, Tommy decides he wants to be a good guy and help out. Both Tommy and us just so happen to pull up the record for ‘Ninja Commandos of Notre Dame.’ We pull up the record first but Tommy is a faster typer. He submits his movie description before us and, shortly after, we save ours. Now things are getting messed up. Data is becoming unpredictable and messy. At the very least, the data could fall out of scheme and not make sense. At worst, it could become corrupted.

That’s why we have database locks. Database management system (Eg. MySQL, MS SQL Server, Oracle, etc…) place locks on a database when someone accesses information in it. In our scenario above, Tommy could not have saved his data because we opened, and therefore locked, the data first. Tommy would have received a message saying the data was locked and he couldn’t change it. After we were done changing the data, that lock would have been removed and Tommy could then make changes.

Data base locks are implemented differently by the various database management systems. They can also change depending on the version of the system too. Some systems may only lock that one specific record we were working on while others may lock the entire row or table. Some database systems may allow users to read data while it is locked while others may refuse access altogether.

We don’t have much control over the locks. It’s beyond the scope to explain how to adjust them and which kind of lock would be best in which situation. I would highly recommend reading the documentation for your chosen database management system and decide what will work best for your design. Understanding what they are and how they work are important though.

Leave a Reply