Mastering Database Management

0 of 78 lessons complete (0%)

Concurrency Control Techniques

Transaction Processing in Distributed Systems

You don’t have access to this lesson

Please register or sign in to access the course content.

A transaction is a logical unit of work constituted by one or more SQL statements executed by a single user. A transaction begins with the user’s first executable SQL statement and ends when it is committed or rolled back by that user.

A remote transaction contains only statements that access a single remote node. A distributed transaction contains statements that access more than one node.

Distributed SQL Statement

SELECT ename, dname FROM scott.emp e, scott.dept@sales.us.americas.acme_auto.com d WHERE e.deptno = d.deptno; 

A distributed update statement modifies data on two or more nodes.

A distributed update is possible using a PL/SQL subprogram unit such as a procedure or trigger that includes two or more remote updates that access data on different nodes.

For example, the following PL/SQL program unit updates tables on the local database and the remote sales database:

BEGIN 
  UPDATE scott.dept@sales.us.americas.acme_auto.com 
    SET loc = 'NEW YORK' 
     WHERE deptno = 10; 
  UPDATE scott.emp 
    SET deptno = 11 
     WHERE deptno = 10; 
END; 
COMMIT;