SQL transaction affecting a big amount of rows

The situation is as follows:

A big production client/server system where one central database table has a certain column that has had NULL as default value but now has 0 as default value. But all the rows created before that change of course still have value as null and that generates a lot of unnecessary error messages in this system.

Solution is of course simple as that:

update theTable set theColumn = 0 where theColumn is null

But I guess it's gonna take a lot of time to complete this transaction? Apart from that, will there be any other issues I should think of before I do this? Will this big t开发者_Python百科ransaction block the whole database, or that particular table during the whole update process?

This particular table has about 550k rows and 500k of them has null value and will be affected by the above sql statement.

The impact on the performance of other connected clients depends on:

• How fast the servers hardware is
• How many indexes containing the column your update statement has to update
• Which transaction isolation settings the other clients connect to the database

The db engine will acquire write locks, so when your clients only need read access to the table, it should not be a big problem.

500.000 records sounds not too much for me, but as i said, the time and resources the update takes depends on many factors.

Do you have a similar test system, where you can try out the update?

Another solution is to split the one big update into many small ones and call them in a loop.

When you have clients writing frequently to that table, your update statement might get blocked "forever". I have seen databases where performing the update row by row was the only way of getting the update through. But that was a table with about 200.000.000 records and about 500 very active clients!

it's gonna take a lot of time to complete this transaction

there's no definite way to say this. Depends a lot on the hardware, number of concurrent sessions, whether the table has got locks, the number of interdependent triggers et al.

Will this big transaction block the whole database, or that particular table during the whole update process

If the "whole database" is dependent on this table then it might.

will there be any other issues I should think of before I do this

If the table has been locked by other transaction - you might run into a row-lock situation. In rare cases, perhaps a dead lock situation. Best would be to ensure that no one is utilizing the table, check for any pre-exising locks and then run the statement.

Locking issues are vendor specific.

Asuming no triggers on the table, half a million rows is not much for a dediated database server even with many indexes on the table.