Client / Server Databases

Overview

https://www.youtube.com/watch?v=JwZPaV2qqqg&list=PLCiOXwirraUDUYF_qDYcZV8Hce8dsE_Ho&index=166

TRC Video

https://www.youtube.com/watch?v=sMkfjWg43cE

What is a Client / Server Database

Where databases are used in a network environment, they are accessed via a database server. This could be a dedicated server if it is a large organisation or it could be running on an existing server. The database server will store and manage the database so can be classified as a DBMS (Database Management System).

The database is connected to the server/DBMS, and all clients must access the database via the server/DBMS.

DBMS

The DBMS is the software that enables the user to define, create and maintain the database and which provides controlled access to the database. It is the software that interacts with the users application programs and the database. It typically provides the following facilities:

1. Data Definition Language (DDL) (Allows users to define the database).
2. Data Manipulation Language (DML) (Allows users to insert, update, delete and retrieve data from the database).
3. Controlled Access to the database.
4. View mechanism.

Client / Server Issues

A client / server database allows multi user access, and the same record can be accessed at the same time by many different users and programs. This causes problems with Concurrent Access & Update. It is also called the lost updates problem.

Lost Update Problem

Resolving Issues

These issues can be solved by using features within the DBMS such as:

Record Locks

One solution is to lock the data to prevent any other changes from taking place while the database is been changed. This will be a temporary restriction on write access to the database, read access can still be given to users because that won't affect the ongoing change.

Once a user requests write access the database is locked, any other requests for write access will be refused. All locks are controlled by DBMS.

More complex locking mechanisms can be used, this could allow access to parts of the database not affected by the edits of another user. This could essentially lock just a specific record or file, and could be used for example to prevent bookings for a specific flight only yet still allow bookings for other flights. Imagine you want to book a flight to Berlin on the 1st June 2017, would you want to lock the whole database if we know which record you want to change. You can leave the same flight on other days unlocked eg, on the 3rd June 2017, and you can leave other flights on the same day unlocked eg, the flight to Paris. Modern online booking systems make you choose a specific seat, and only this will be locked for a period of time to prevent someone booking the same seat. This will normally be the session time out value, normally about 15 minutes.

Serialisation

Serialisation ensures only one transaction at a time is executed.

Serialisation is managed by DBMS and it ensures each transaction is carried out in the correct sequence. Without serialisation it could be pure chance that controls who has write access to the database. Those who request write access at the precise moment in time the database becomes unlocked, will get write access. If the correct sequence of requests is maintained then the DBMS can ensure all transactions are completed and in the correct order.

The DBMS will identify which transactions need serialising and provide a schedule for dealing with them.

Timestamp Ordering

Commands executed based on the timestamp for when the data was last written or read.

Each transaction will have a read and a write timestamp, these will then be used to check that the database is unchanged before it writes data into the database. For example:

1. If transactions 1 reads the data, and then pauses for 15 seconds
2. But then transaction 2 reads the data and then writes its data back
3. If transaction 1 then attempts to write its data back it will fail
4. The timestamp will show that the data read by transaction 1 is out of date

Commitment Ordering

In general all transactions are execute in order they are received, however each transactions will be assessed for the nature of the transaction. The main factor considered will be the transactions impact on the database. A Transaction which will cause a deadlock will be blocked until any dependant action is complete.

An algorithm is used to create a commit order which avoids conflicts between transactions.