09 - Advance Database concepts

9.1 Introduction

In this chapter we look at some advanced database concepts.

9.2 transaction

We already looked at the concept of transaction in chapter 2. We look a t bunch of data manipulations as one lot and call it as one transaction. We also looked at the process of commiting – making the changed we do to the database permanent.

For it to be completed the entire set of statements need to be processed as one batch – or none of the statements should be processed.

For example:

You withdraw money from one account and deposit to another account.

Now this has to be treated as one transaction. If after making the withdrawal of the money permanent – this is called ‘committing – if you didn’t make the deposit to the other account also permanent then there would be confusion. The money that you withdraw will gpo without a trace in the database.

So it has to happen on the all or nothing basis. Either both the transaction need to go in or nothing should go in.

So in your programming you must indicate to the database that you are starting the transaction and also ending the transaction. Some databases have the begin and end transaction statements. Others like Oracle start the transaction when the first transaction is done and end it when the commit statement is issued. Instead of a commit you can also rollback - asking all the changes you have done to the database so far to be undone.

So the first phase of the commit is to do the transactions and then the second is to write the changes permanently to the database.

9.2 locking

We looked at the concept of Looking also in chapter 2.

When you want to change some data in the database you must make sure that you see the latest version and that what you change is visible to others only after you commit the change and before they begin their transaction.

For example let’s say you want to give an increment of 5000USD to Peter who is currently getting a salary of 35,000USD. You select the record and while you are in the process of changing it another person also selects the record and changes it. So the results may become unpredictable. Either he might get 45,000USD (an additional 5000USD – and he will love the concept of ‘non locking’ databases !) So once you select that particular record that should become unavailable to the other people until you release it.

The database must lock the record and should not release it until you have released it – so that the changes you have done are permanent and the other person is changing the record after seeing your change.

9.3 dead locking

There is user 1 and user 2 and there is resource 1 and resource 2.

User 1 has locked resource 1.

User 2 has locked resource 2.

After doing some steps, as part of their transactions User 1 now requires Resource 2 and User 2 now requires resource 1.

So each user will have to wait till the other user has completed the transaction.

So this is called the dead lock. Each user has dead locked the others user. They cannot come out of this unless each has completed his transaction. And for one user to complete the transaction the other user must complete the transaction. Chicken and egg situation.

Modern databases are equipped to identify and kill dead locks - the users will be thrown out of their session with a message saying that a deadlock has been detected.

9.4 Cleint server databases

Cleitn server model is where the database is situated in the server and many clients access the database via the network. The need is therefor for more concurrency control and network availability. One database server serves many clients

9.5 Web Databases

A little extended version of the client server model – where the database sits in a central server and is accessed via a network – the internet. There is even more need for concurrency control.

Like us on Facebook