I've got a load of pink toys here but I prefer blue. It'd be nice if we could
change the color like magic. In databases you can, with an update. An update
statement overwrites existing values in your table. To do this use a 'set' clause to
specify each column and its new value. You can do this for as many columns of
the table as you want. Just remember to include a where clause or you'll update
every row in the table. Forgetting the where clause creates some problems.
Firstly it's rare you want everything to have the same value. Secondly updating
the whole table will take some time, particularly if the table is large. This
can bring a third problem. Only one person at a time can change a row. Once
you start an update no one else can change the rows your SQL affects. Even
if they want to change the values in different columns to you they have to
wait until you're finished. And by finished I mean committed. If for
some reason you forget to commit other people wanting to change these rows
still have to wait. This can quickly bring your database grinding to a halt.
So to avoid this you need to commit after every single update, right? Not so
fast. Sometimes you want to change several
tables or rows at the same time. And it's important that either all the changes
happen or none of them. Let's say I owe you some money and I'm finally getting
around to paying you back. When I take the money out of my wallet we need to update
it to decrease the remaining cash, then when you get the money, run another
update to increase the amount in yours. If we commit the change to each wallet
immediately we can end up in a mess. Say there's a disaster between taking the
money out of my wallet and adding it to yours. So we can't complete the transfer. If
this happens after we commit the change to mine, neither of us have the cash. To
ensure this doesn't happen either we want to ensure we update both wallets, or
neither. This is our transaction. The smallest unit of work which leaves our
data in a consistent state. You commit the changes after updating both wallets
completing the transaction. Not before or everyone could end up with no money.
Often your transactions will insert or update multiple tables. If you have
different transactions affecting the same tables you need to take care to
ensure you avoid deadlock. Deadlock happens when two people each hold one
resource, and then they want to get the resource the other person has.
Neither is prepared to let go of what they have, but they can't continue until
they've got the resource from the other person. Deadlock. Oracle database
automatically detects deadlocks and forces one session to stop requesting
the other item, throwing an exception. This is an extreme way to solve the
problem that you need to code around or you're going to upset your users. But the
correct way to deal with deadlocks is to ensure that they never happen in the
first place. There are a couple of steps you can take to do this. First ensure all
your transactions change tables in the same order. This can be hard to coordinate
in large applications, so make sure you talk about this with your fellow
developers and test thoroughly. Secondly you can mark your intent to change rows
with 'select for updates'. This locks the rows the query finds until your
transaction completes. Doing this at the start ensures you'll be able to make
your changes when you finally come to your updates. This is particularly useful
in long-running transactions which affect many tables. This can still lead
to a lot of waiting, but at least everything will work. A final note on
deadlocks; unlike in some databases, in Oracle only changing rows
takes out locks. Select statements never block others, unless you add the 'for
update' clause. So you've avoided deadlocks there's one
more thing you need to watch for with updates and that's if they go missing.
Most applications these days are stateless. This means reading data out of
a table and writing changes back to it are separate transactions. This can lead
to problems if two people are working on the same data at the same time. Say
Sequela is trying to deactivate the Death Query and Jason just wants to make
it look pretty. Both read the data out at the same time. Sequela switches the
weapon off and saves her changes. A few moments later Jason makes it a funky
red. Now here's where things get interesting. The data Jason sent to the
database still has the weapon armed. So if your code updates all the columns,
Sequela's changes are lost. To avoid this you need to ensure no one else has
change the data since you last read it. You do this using optimistic locking.
There are several ways to do this. A common one adds a version number to each
row. All clients must select this out when getting the data and then pass it
back when saving. Your updates then check the version number is still the same. If
it is you update the columns and increment the version. Anyone who read
the data before your updates will still have the old version number. So when they
save nothing happens. You need to detect that the update did nothing, then inform
your users someone else made changes and check of theirs are still valid. This
stops people continually overwriting each other's updates. Deadlock and lost
updates are both issues which can lead to intense frustration from people who use
your application, so always test what happens when two people use your
application at the same time. And code defensively to
prevent these problems. You also need to take care if people can delete data in
multi-user environments. We'll look at the ins and outs of wiping data in the
next episode. Thanks for watching! Subscribe to my youtube channel to learn
more about database development and see SQL magic.