Reputation: 13766

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

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

Upvotes: 2

Answers (6)

Jeroen Landheer

Reputation: 9943

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.

Upvotes: 1

TrayMan

Reputation: 7445

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.

Upvotes: 1

Andriy Volkov

Reputation: 18933

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.

Upvotes: 1

Eric Petroelje

Reputation: 60559

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.

Upvotes: 3