Connection Management

A DBMS that is separate from its users is known as a database server. To enable the users to access the server a mechanism called connection management is required. This connection management is accessed by SQL statements. Even local database servers are accessed in this way.

A user may need to make and break connections several times to achieve required transactions.

CONNECT region1 USER stuart;

CONNECT region2 USER stuart;

SET CONNECTION region1;                //region1 becomes CURRENT CONNECTION

(some sql transaction statements)

SET CONNECTION region2;                //region2 becomes CURRENT CONNECTION region1 becomes DORMANT CONNECTION

(some more sql transaction statements)

SET CONNECTION region1;                //region1 becomes CURRENT CONNECTION region2 becomes DORMANT CONNECTION

(sql statements to complete transaction)

DISCONNECT ALL                            //Disconnects user from all DBMS

There can be only one current connection.

Data management and operation is autonomous to each server. It may be neccessary in system design to ensure that data that may be transferred has identifiers that are unique across the system i.e. different student identifier ranges for each region.

Transaction Management

The Two Phase Commit

One server has control. In the image below this is UserA. An SQL transaction statement is sent by UserA to both SystemB and SystemC.

UserA begins the commit by starting to log the transaction.

UserA then interrogates both systems to ascertain if they are ready to commit.

If both systems are ready they prepare to COMMIT or ROLLBACK the transaction.

Both systems should reply whether they are ready. Yes or No. If the answer is Yes, a log is started by the system.

UserA awaits the replies:

• All replies are Yes - UserA sends COMMIT signal to all systems.
• If just one reply is No - UserA sends a ROLLBACK signal to all systems.

A time-out is the equivalent of a No. UserA logs all responses regardless of being Yes or No.

On receipt of the COMMIT or ROLLBACK signal both systems have to comply with the transaction instructed. Log entries are made.

Both systems should reply OK. UserA returns SQLSTATE values upon completion.

Two phase commit causes a considerable increase in network traffic with a detrimental effect on response times. There are a total of messages that are 4 * number of systems. e.g. UserA has 3 servers to update - this will cause a minimum of 12 messages.

Comments, suggestions, ideas to
Stuart Banner