World Wide Tutorial

1. Introduction
At what point does it go from a DBMS micro-type file server as dBase, Paradox, FoxPro and Access, a heavyweight client / server as Oracle, Sybase, SQL Server, Informix and DB2?

We assume that the reader of this article a little control the world of databases. We assume he knows what a request (even if it has ever written), what a competitive access to data (two users wishing to modify the same data for example), and that is familiar with the concept table, column (field) and registration. Moreover, we demand to know the minimum about operating systems, to know what a file, a byte, a client, server and a network…

Assume then the next question:
What difference is there between a DBMS-based files and a database in C / S?

In fact, there are two fundamental differences between the two systems. All the techniques of C / S is dedicated to minimize data traffic on a network and ensure greater integrity in processing data…

To achieve this, the C / S uses several technologies, one on the way of locking in the competition for access, other modes of data processing. We will be examining all of these techniques and take some concrete examples to make tangible our words.

Note already a fundamental difference: while in a system based files is each workstation, which deals locally data in a database C / S, all treatments are normally carried out on the server through queries.

2. The lock mode
The lock mode is an essential function of the database. So far, there are only two distinct modes: locking "optimistic" and blocking "pessimistic".

In life it is possible that several people read, watch, listen, the same thing at the same time (newspapers, radio, television…) but it is impossible that several people create the same information simultaneously. For example, it is impossible for two writers to keep the same pen to write the same novel as it is impossible for two speakers to speak at the same time the same things without it becoming cacophonique.
In short, writing information is a solitary act, but his reading can be accomplished by many people simultaneously.

In the world of databases, the problem is the same.
It is therefore appropriate to provide a mechanism capable of coping with simultaneous access to reading for all users, and to prevent multiple users to create, modify or delete the same.

To solve this problem, researchers have invented algorithmic modes locking pessimistic and optimistic that we will now detail…

2.1. Lock pessimistic
When a user wants to modify a given, the DBMS tries to put a lock for writing. Either the transaction is effective (and therefore the lock is set) or the operation failed because another user has already installed a lock. If the operation is positive, throughout the duration of the amendment, the lock is active for this user until the valid user changes his or cancelled. In both cases the lock is released.

2.2. Lock optimistic
When a user wants to modify a given, the DBMS is the version number of registration and poses no lock. Multiple users can do the same without any limit. The first user that validates its changes, increasing the version number of registration. All others are denied their amendment, because the version number they have observed at the beginning of the editing session data is not the same as they can read at the end of this session.

2.3. Discussion on these different modes of locking
When using the technique of locking pessimistic, it is advisable not to "throw" the user when it tries to put a lock to modify the data. In this case, the DBMS makes a loop waiting trying to put the lock for n seconds. If it fails, it informs the user and recommends him to renew his attempt a little later…
That's how it works almost all DBMS-based files (dBase, Paradox, FoxPro, Access…).

It seems a priori obvious that the mode locking pessimistic should be preferred in all cases… It is, however, forget the basic principle of C / S: minimize the burden of the network…

Or make a loop waiting interviewed a file, monopolizes the network dramatically. That is why almost all DBMS C / S works by blocking optimistic, because in this mode, the exchange of data between the base and the client is quick and brief…

In addition, if we take into account referential integrity and that the structure of the database allows you to modify existing key passing their values in all tables daughters, then in the case of a database file may have come to get an intense network traffic while in the case of client server, the operation will be carried out within the DBMS therefore without any influence on the charge of the network.

3. The SQL C / S
There is no difference between a SQL database available for a type of file and a database-type C / S: SQL levels are also poor or rich depending on the quality of the editor of DBMS… But there is two fundamental differences in enforcement orders SQL and monitoring transactions…

3.1. Execution and SQL
However, there is a fundamental difference in enforcement of these requests. In fact through a database file-type, it always runs the request of locally while in a DBMS C / S it executes the query on the server.
The difference is fundamental in terms of network traffic congestion… and we must not forget that basis, whether to file or C / S, all access to data are through requests…

To understand the difference here is a simple example. A table name CLIENT contains 50 000 recordings of 200 bytes each. The request is as follows:

SELECT * FROM CUSTOMER WHERE (CLI_NAME like 'CLINTON%%')

In a database file-type, here is the flow of data:
♦The PC repatriation since the file server containing the table CLIENT (Or at least 50 000 x 200 bytes (not counting the index and the rest [1])
♦The PC deals locally complaint
♦The PC displays the result (for example 5 lines of 200 bytes)

In a DBMS type C / S, following the flow of data:
♦The PC server sends to the text file the complaint (about 50 bytes)
♦The server runs the application on the table CLIENT
♦The server sends the PC data replying to the query (Nos. 5 lines 200 bytes)
♦The PC displays the result

Review of these operations:
DBMS file: about 10 001 000 bytes were transmitted over the network
DBMS C / S: approximately 1 050 bytes were transmitted over the network

In this case, the ratio is nearly 1 / 10000 for the C / S…

3.2. The transactional
In addition, the DBMS C / S have a mechanism that is generally not available in the database file, the transaction.

The transaction brings together multiple SQL commands as if it were an instruction-type atomic ie running in full or not (as the division of two numbers on the computer microprocessor ).
This involves giving a starting point in the transaction a point of arrival and check whether all SQL commands have been carried out successfully. In the case or any of SQL was not treated properly, then the final point of the transaction will go back over all the operations already carried out. The transaction is then as a single instruction type 'all or nothing'.
This is possible only on servers capable of recording all insertions, deletions and amendments made on registration tables of a database (historicization changes).

An example will show us how to present a session of transaction:
Suppose we have a centralized database on an SQL server in C / S to collect customer data and orders. Suppose also that the commissioners are equipped with laptops and that every week they come at the company's headquarters to fill the central base of new customers and orders that they have captured on their mobile.
To achieve this gluing information, the computer has completed the transaction follows:

BEGIN TRANSACTION
INSERT INTO CENTRAL_CLIENT (NO_CLI, NOM_CLI, ADRESSE_CLI)
SELECT PC.NO_CLI, PC.NOM_CLI, PC.ADRESSE_CLI
FROM PORTABLE_CLIENT PC
INSERT INTO CENTRAL_COMMANDE (NO_CLI, NO_COM, MONTANT_COM)
SELECT PC.NO_CLI, PC.NO_COM, PC.MONTANT_COM
FROM PORTABLE_COMMANDE PC
IF ERROR
THEN
ROLLBACK
ELSE
COMMIT
ENDIF

Had he done qu'enchaîner the two complaints, he would have been possible for customers to be inserted without the orders or…
Worse, the system could try to insert commands inserted by failing to customers.
Moreover, the two complaints could have been interverties… But all good DBMS, whether C / S or file, know how to manage the intégrités referential and would have prevented any insertion in the table of orders without the reference to the customer previously inserted in the table of customers…

The trigger with SQL commands will be called within a broader transaction, including the abolition of registration in the table mother. The SQL trigger this demanding deletion from the tables FACT, LIGNFACT and BONLIV all records referring to identifying the client sent as a parameter. If at least one query fails, the trigger False reference to the procedure have called and all applications will be cancelled. Otherwise, if everything goes well, the trigger referral True and all the changes will be implemented by a validation (COMMIT).

The trigger is stored within the definition of the database and runs on the server, whenever a suppression order arrives at the table CUSTOMER. So there is no traffic on the network to make this treatment…

5. Stored procedures, treats the C / S
In addition to the triggers, stored procedures can store a treatment (usually operate only on data from the base) within the database, which will run through the appeal procedure in a timely manner, on the server.

Example: An application calls for a treatment modification selling prices for products based on the change of a number of stock indices. This operation can be triggered manually by the head of the department…
The code could be as follows:

CREATE PROCEDURE AUGMENTATION_PRIX
VAR
INDICE REAL
ENDVAR
; calculating the new index increase,
; the average difference between the old and new indices
SELECT AVG(DERNIER_INDICE – INDICE_PRECEDENT) AS :INDICE
FROM INDICE_BOURSE
; testing if it is d ' an increase rather than a decrease in other words a positive indication and non-negative
IF INDICE < 0
THEN
RETURN
ENDIF
; conducts an increase
UPDATE PRODUITS
SET PRIX_VENTE_HT = (PRIX_VENTE_HT * (1 + (:INDICE)))
IF ERROR
THEN
RETURN FALSE
ENDIF
RETURN TRUE
Since this procedure can be called anytime, by any client… It will run on the server and will require virtually no traffic network.
One can also imagine that it is coupled with a trigger or performed an hour delayed, for example at night so as not to slow down data access in general more frequent in the day…

6. The newspaper feature of DBMS C / S
In addition to the triggers and stored procedures, most DBMS C / S offer an historical "life" of data, in what is called a transaction log.
It keeps track of all time insertions, deletions, changes in the database since its inception.
This newspaper can go back in part on the data. The main interest lies in being able, in case of crash, revert to a state data satisfactory.

Take the example of a transaction described in paragraph 2.2.
To ensure proper transaction consists of 2 requests to update the database to perform the following sequence:

wrote in the newspaper a label identifying the beginning of the transaction (synchronization)
1 queries: copy data recordings before changes in the newspaper
1 queries: copy data from recordings after amendment in the newspaper
2 queries: copy data recordings before changes in the newspaper
2 queries: copy data from recordings after amendment in the newspaper
wrote in the newspaper a label indicating that the transaction was a success or a failure
reflects the changes of data in tables
wrote in the newspaper a label indicating the end of the transaction
In this way the system is capable failure occurring during a transaction return to a stable and consistent data to ensure the integrity and therefore the atomicity of the transaction.
To return to this stable condition, the system reads the newspaper starting from the end and takes all transactions that have been successful without the updated physical data has been made.

7. Conclusion
Can I access the data base queries away?
A negative points systems C / S is that all treatments on the basis of data must be, at one time or another, made by requests. But with systems based on the file it is often possible to access the data directly, free queries, and proceed to a treatment using, for example, a reading sequential recordings of a table.
In general DBMS type files offer tools (4GL, components…) to achieve this kind of treatment with no time to appeal the requests. In this case it is desirable to know the internal format of the base and its specific [2].
Such access is interesting and very flexible in the case database of low volume. But it does not updated in a single transaction or request more tables… Another advantage: it is independent of the quality of SQL provided. In the case of "middleware [3]" as the engine of BDE Inprise (former Borland) it helps to unify the treatment whatever the database underlying and its platform. It can theoretically change DBMS without having to rewrite a single line of application code…
In the case of DBMS C / S it is still possible to make treatment in the local language programming using the technique of "cursor" (cursor): the cursor is an order that allows SQL stamped data d ' a table in memory and to access data through variables to make a treatment.

Is it possible to bypass the lock mode optimistic to make pessimistic?
Of course yes. But doing so systematically is a heresy, especially in the case of a database where the volume of transactions and the rate of updated data would be very important: Block interface it occur frequently and may even lead to paralysis of the entire system.
However achieve a pessimistic lock is very simple: just create within the database table semaphore containing the user name, the name of the table, references to the registration lock ( for example his' ROW_ID 'if the server is able to provide). Before any updating or deleting just see the semaphore table, and if registration is not appears to be inserted to make the change, then delete it from the table semaphore. But we must guard against several small underlying problems: for example how to delete locks become obsolete? This can happen, especially when a user has started an amendment and that his client was the victim of a hardware failure on cutting network…

The SQL is there a standard language?
Yes, of course. There is a standard definition of SQL (SQL 2: ISO / ANSI 92). But every publisher is free to rest or not the norm. The result is a strong incompatibility between different publishers DBMS, which can possibly bypass is relying solely on the elements common to major publishers (very limited) or by using a standard middleware which in this case will d ' write all requests for assistance from local SQL and ensure its translation into SQL-specific database.
Another advantage of some middleware is proposing to make requests heterogeneous, ie between different databases, different database, possibly even run on different platforms system. This is particularly true engine of BDE Inprise (former Borland).

What language is used to write stored procedures and triggers?
Unfortunately there is no common language between different publishers. Further developments RDBMS technologies to the object and web are significantly change the structures of languages of different publishers from one version to another.
For example, Oracle now bears his famous language PL / SQL in the world of the object and provides specialized modules (libraries of functions) for example, access to technical web or making piloting GIS (Information Systems Geographic).
The standard SQL 3 is trying to solve a number of these problems.

What kind of machine is required to run a RDBMS C / O?
It is necessary to have a powerful server, with a processor rather specialized in the treatment of basic data (text and number) that the graphics or sound…
The amount of memory needed is obviously much more important than through a database file (such as NT operating system, a RDBMS C / S requires a minimum of 128 megabytes of RAM even with a very limited number of users). In both cases we must have access to disks very fast (UW SCSI for example) and not to hesitate to invest in a RAID controller possibly in "hot plug".

Below is a table that can help you in your choice of passage from one to another: