Do I need to "lock" a Sql Table during update job?

Question

I have a web App running and I want to access the sql db with a another app to update one of the tables with new calculations. Will my users notice anything during the updates? WIll I get an error if they access at the same time I do updates, or is there auto locking ? tia

Answer 1

If your RDBMS is any good, it should take care of it for you.

You probably want to make sure "autocommit" is off, and only commit when you're finished your update, though.

Answer 2

There is no autolocking. If what you want to do is quick, then no need to autolock, since probability of multiple access is really low.

However, if you plan to run some long update, you should seriously consider placing a Maintenance page to the front page while you make the updates.

Answer 3

Generally the rows would be locked while you do the updates and any SQL query that might use those rows will "hang" until your update completes, then return the correct data.

There are some edge-case exceptions to this, but generally speaking I wouldn't worry about it unless you are doing some really fancy long-running updates that will cause the data to be locked for a long period of time.

Answer 4

Depending how lucky they are, they will either see the values before the update or after the update. Usually that's not a problem so you can ignore the locking problem altogether. They won't get an error though, unless they try to lock the tables themselves.

Answer 5

That depends on what DBMS you have and what isolation level you are using. See here for what PostgreSQL documentation has to say. Basically, there is a number of different ways that updates can be handled. At one end, reads that happen at the same time as updates may get inconsistent data from a partially committed update. At the other end, transactions are fully synchronized. Most DBMS's tend to do something in between for reasons of efficiency.

Answer 6

If you use an RDBMS with the ability of transactions, you don't need to do locking, it will be done for you. If not, you either need to lock the row or the table depending of which one you use.

Most RDBMS systems do have transaction support though, so this shoudln't be something to worry about.