9/18/2019

Introduction to
Transaction concepts
Unit 2

Prepared by Shimi Biju 1

Transaction
 A transaction is a logical unit of work that contains one or more
SQL statements
 A transaction can be defined as a group of tasks. A single task is
the minimum processing unit which cannot be divided further.

Prepared by Shimi Biju 2

1
 9/18/2019

 A’s Account
 Let’s take an example of a
simple transaction.
Open_Account(A)
 Suppose a bank employee Old_Balance = A.balance
transfers Rs 500 from A's New_Balance = Old_Balance - 500
account to B's account. This A.balance = New_Balance
very simple and small Close_Account(A)
transaction involves several
low-level tasks.  B’s Account
Open_Account(B)
Old_Balance = B.balance
New_Balance = Old_Balance + 500

Prepared by Shimi Biju

B.balance = New_Balance 3

Close_Account(B)

ACID Properties

 A transaction is a very small unit of a program and it may contain

several lowlevel tasks.
 A transaction in a database system must
maintain Atomicity, Consistency, Isolation, and Durability −
commonly known as ACID properties − in order to ensure
accuracy, completeness, and data integrity.

Prepared by Shimi Biju 4

2
 9/18/2019

Atomicity
 This property states that a transaction must be treated as an
atomic unit, that is, either all of its operations are executed or
none.
 There must be no state in a database where a transaction is left
partially completed.
 States should be defined either before the execution of the
transaction or after the execution/abortion/failure of the
transaction.

Prepared by Shimi Biju 5

Consistency
 The database must remain in a consistent state after any
transaction.
 No transaction should have any adverse effect on the data
residing in the database.
 If the database was in a consistent state before the execution of a
transaction, it must remain consistent after the execution of the
transaction as well.

 For example, in an application that transfers funds from one

account to another, the consistency property ensures that the total
value of funds in both the accounts is the same at the start and end
of each transaction.

Prepared by Shimi Biju 6

3
 9/18/2019

Isolation
 In a database system where more than one transaction are being
executed simultaneously and in parallel, the property of isolation
states that all the transactions will be carried out and executed as
if it is the only transaction in the system.
 No transaction will affect the existence of any other transaction.
 For example, in an application that transfers funds from one
account to another, the isolation property ensures that another
transaction sees the transferred funds in one account or the other,
but not in both, nor in neither.

Prepared by Shimi Biju 7

Durability
 The database should be durable enough to hold all its latest
updates even if the system fails or restarts.
 If a transaction updates a chunk of data in a database and
commits, then the database will hold the modified data.
 If a transaction commits but the system fails before the data could
be written on to the disk, then that data will be updated once the
system springs back into action.

 For example, in an application that transfers funds from one

account to another, the durability property ensures that the
changes made to each account will not be reversed.

Prepared by Shimi Biju 8

4
 9/18/2019

What is Concurrency Control?

 Concurrency control is the procedure in DBMS for managing
simultaneous operations without conflicting with each another.
 Concurrent access is quite easy if all users are just reading data.
 There is no way they can interfere with one another.
 Though for any practical database, would have a mix of reading and
WRITE operations and hence the concurrency is a challenge.
 Concurrency control is used to address such conflicts which mostly
occur with a multi-user system.
 It helps you to make sure that database transactions are performed
concurrently without violating the data integrity of respective
databases.
 Therefore, concurrency control is a most important element for the
proper functioning of a system where two or multiple database
transactions that require access to the same data, are executed
simultaneously.
Prepared by Shimi Biju 9

Concurrency control Manager

 The concurrency control manager coordinates the actions
of interactive access to the database by concurrently running
transactions.
 The goal of concurrency control is to coordinate execution so that
the VIEW or effect from the database's perspective is the same as
if the concurrently executing transactions were executed in a
serial fashion.

Prepared by Shimi Biju 10

5
 9/18/2019

Methods for concurrency control

 In a multiprogramming environment where multiple transactions
can be executed simultaneously, it is highly important to control
the concurrency of transactions.
 We have concurrency control protocols to ensure atomicity,
isolation, and serializability of concurrent transactions.
 Concurrency control protocols can be broadly divided into two
categories −
 Lock based protocols
 Time stamp based protocols

Prepared by Shimi Biju 11

Lock-based Protocols
 Database systems equipped with lock-based protocols use a
mechanism by which any transaction cannot read or write data
until it acquires an appropriate lock on it.
 Locks are of two kinds −
 Binary Locks − A lock on a data item can be in two states; it is
either locked or unlocked.
 Shared/exclusive − This type of locking mechanism
differentiates the locks based on their uses.
 If a lock is acquired on a data item to perform a write operation, it
is an exclusive lock.
 Allowing more than one transaction to write on the same data
item would lead the database into an inconsistent state.
 Read locks are shared because no data value is being changed.
Prepared by Shimi Biju 12

6
 9/18/2019

Shared Lock (S):

 A shared lock is also called a Read-only lock. With the shared

lock, the data item can be shared between transactions. This is
because you will never have permission to update data on the data
item.
 For example, consider a case where two transactions are reading
the account balance of a person. The database will let them read
by placing a shared lock. However, if another transaction wants to
update that account's balance, shared lock prevent it until the
reading process is over.

Prepared by Shimi Biju 13

Exclusive Lock (X):

 With the Exclusive Lock, a data item can be read as well as

written. This is exclusive and can't be held concurrently on the
same data item. X-lock is requested using lock-x instruction.
Transactions may unlock the data item after finishing the 'write'
operation.
 For example, when a transaction needs to update the account
balance of a person. You can allow this transaction by placing X
lock on it. Therefore, when the second transaction wants to read
or write, exclusive lock prevent this operation.

Prepared by Shimi Biju 14

7
 9/18/2019

Lock protocols
 There are four types of lock protocols available −
1. Simplistic Lock Protocol
2. Pre-claiming Lock Protocol
3. Two-Phase Locking 2PL
4. Strict Two-Phase Locking

 Simplistic lock-based protocols allow transactions to obtain a

lock on every object before a 'write' operation is performed.
 Transactions may unlock the data item after completing the
‘write’ operation.

Prepared by Shimi Biju 15

Pre-claiming Lock Protocol

 Pre-claiming protocols evaluate their operations and create a list

of data items on which they need locks.
 Before initiating an execution, the transaction requests the system
for all the locks it needs beforehand.
 If all the locks are granted, the transaction executes and releases
all the locks when all its operations are over.
 If all the locks are not granted, the transaction rolls back and
waits until all the locks are granted.

Prepared by Shimi Biju 16

8
 9/18/2019

Two-Phase Locking 2PL

 This locking protocol divides the execution phase of a transaction into
three parts.
 In the first part, when the transaction starts executing, it seeks
permission for the locks it requires.
 The second part is where the transaction acquires all the locks.
 As soon as the transaction releases its first lock, the third phase starts.
 In this phase, the transaction cannot demand any new locks; it only
releases the acquired locks.

Prepared by Shimi Biju 17

 The Two-Phase Locking protocol allows each transaction to make

a lock or unlock request in two steps:
 Growing Phase: In this phase transaction may obtain locks but
may not release any locks.
 Shrinking Phase: In this phase, a transaction may release locks
but not obtain any new lock

Prepared by Shimi Biju 18

9
 9/18/2019

Strict Two-Phase Locking

 The first phase of Strict-2PL is same as 2PL.
 After acquiring all the locks in the first phase, the transaction continues
to execute normally.
 But in contrast to 2PL, Strict-2PL does not release a lock after using it.
 Strict-2PL holds all the locks until the commit point and releases all the
locks at a time.

Strict-2PL does not have cascading abort as 2PL does.

Prepared by Shimi Biju 19

Timestamp-based Protocols

 The timestamp-based algorithm uses a timestamp to serialize the

execution of concurrent transactions.
 This protocol ensures that every conflicting read and write
operations are executed in timestamp order.
 The protocol uses the System Time or Logical Count as a
Timestamp.
 The older transaction is always given priority in this method.
 It uses system time to determine the time stamp of the transaction.
This is the most commonly used concurrency protocol.
 Lock-based protocols help you to manage the order between the
conflicting transactions when they will execute.
 Timestamp-based protocols manage conflicts as soon as an
operation is created.
Prepared by Shimi Biju 20

10
 9/18/2019

Deadlock
 In a multi-process system, deadlock is an unwanted situation that arises
in a shared resource environment, where a process indefinitely waits for
a resource that is held by another process.

 For example, assume a set of transactions {T0, T1, T2, ...,Tn}. T0 needs
a resource X to complete its task. Resource X is held by T1, and T1 is
waiting for a resource Y, which is held by T2. T2 is waiting for resource
Z, which is held by T0. Thus, all the processes wait for each other to
release resources. In this situation, none of the processes can finish their
task. This situation is known as a deadlock.

 Deadlocks are not healthy for a system. In case a system is stuck in a

deadlock, the transactions involved in the deadlock are either rolled
back or restarted.

Prepared by Shimi Biju 21

Deadlock Prevention

 To prevent any deadlock situation in the system, the DBMS

aggressively inspects all the operations, where transactions are
about to execute. The DBMS inspects the operations and analyzes
if they can create a deadlock situation. If it finds that a deadlock
situation might occur, then that transaction is never allowed to be
executed.
 There are deadlock prevention schemes that use timestamp
ordering mechanism of transactions in order to predetermine a
deadlock situation.

Prepared by Shimi Biju 22

11
 9/18/2019

Wait-Die Scheme
 In this scheme, if a transaction requests to lock a resource (data item),
which is already held with a conflicting lock by another transaction,
then one of the two possibilities may occur −

 If TS(Ti) < TS(Tj) − that is Ti, which is requesting a conflicting lock, is

older than Tj − then Ti is allowed to wait until the data-item is
available.

 If TS(Ti) > TS(tj) − that is Ti is younger than Tj − then Ti dies. Ti is

restarted later with a random delay but with the same timestamp.

 This scheme allows the older transaction to wait but kills the younger
one.

Prepared by Shimi Biju 23

Wound-Wait Scheme
 In this scheme, if a transaction requests to lock a resource (data item), which is
already held with conflicting lock by some another transaction, one of the two
possibilities may occur −

 If TS(Ti) < TS(Tj), then Ti forces Tj to be rolled back − that is Ti wounds Tj.
Tj is restarted later with a random delay but with the same timestamp.

 If TS(Ti) > TS(Tj), then Ti is forced to wait until the resource is available.

 This scheme, allows the younger transaction to wait; but when an older
transaction requests an item held by a younger one, the older transaction forces
the younger one to abort and release the item.

 In both the cases, the transaction that enters the system at a later stage is
aborted.

Prepared by Shimi Biju 24

12
 9/18/2019

Deadlock Avoidance

 Aborting a transaction is not always a practical approach.

 Instead, deadlock avoidance mechanisms can be used to detect
any deadlock situation in advance.
 Methods like "wait-for graph" are available but they are suitable
for only those systems where transactions are lightweight having
fewer instances of resource.
 In a large system, deadlock prevention techniques may work well.

Prepared by Shimi Biju 25

Wait-for Graph
 This is a simple method available to track if any deadlock
situation may arise.
 For each transaction entering into the system, a node is created.
 When a transaction Ti requests for a lock on an item, say X,
which is held by some other transaction Tj, a directed edge is
created from Ti to Tj.
 If Tj releases item X, the edge between them is dropped and Ti
locks the data item.

 The system maintains this wait-for graph for every transaction

waiting for some data items held by others. The system keeps
checking if there's any cycle in the graph.

Prepared by Shimi Biju 26

13
 9/18/2019

Prepared by Shimi Biju 27

 Here, we can use any of the two following approaches −

 First, do not allow any request for an item, which is already
locked by another transaction. This is not always feasible and
may cause starvation, where a transaction indefinitely waits for a
data item and can never acquire it.
 The second option is to roll back one of the transactions. It is not
always feasible to roll back the younger transaction, as it may be
important than the older one. With the help of some relative
algorithm, a transaction is chosen, which is to be aborted. This
transaction is known as the victim and the process is known
as victim selection.

Prepared by Shimi Biju 28

14
 9/18/2019

Serializability
 When multiple transactions are running concurrently then there is
a possibility that the database may be left in an inconsistent state.
 Serializability is a concept that helps us to check
which schedules are serializable.
 A serializable schedule is the one that always leaves the database
in consistent state.
 A serial schedule doesn’t allow concurrency, only one transaction
executes at a time and the other starts when the already running
transaction finished.

Prepared by Shimi Biju 29

Types of Serializability
 There are two types of Serializability.
1. Conflict Serializability
2. View Serializability

Prepared by Shimi Biju 30

15
 9/18/2019

Conflict Serializability
 IT is one of the type of Serializability, which can be used to check
whether a non-serial schedule is conflict serializable or not.
 What is Conflict Serializability?
 A schedule is called conflict serializable if we can convert it into
a serial schedule after swapping its non-conflicting operations.
 Conflicting operations
 Two operations are said to be in conflict, if they satisfy all the
following three conditions:
 1. Both the operations should belong to different transactions.
2. Both the operations are working on same data item.
3. At least one of the operation is a write operation.

Prepared by Shimi Biju 31

 Lets see some examples to understand this:


Example 1: Operation W(X) of transaction T1 and operation R(X) of
transaction T2 are conflicting operations, because they satisfy all the three
conditions mentioned above. They belong to different transactions, they are
working on same data item X, one of the operation in write operation.

 Example 2: Similarly Operations W(X) of T1 and W(X) of T2 are conflicting

operations.

 Example 3: Operations W(X) of T1 and W(Y) of T2 are non-conflicting

operations because both the write operations are not working on same data
item so these operations don’t satisfy the second condition.

 Example 4: Similarly R(X) of T1 and R(X) of T2 are non-conflicting

operations because none of them is write operation.

 Example 5: Similarly W(X) of T1 and R(X) of T1 are non-conflicting

operations because both the operations belong to same transaction T1. 32
Prepared by Shimi Biju

16
 9/18/2019

DBMS View Serializability

 What is View Serializability?

 View Serializability is a process to find out that a
given schedule is view serializable or not.
 A Schedule is called view serializable if it is view equal to a
serial schedule (no overlapping transactions).

Prepared by Shimi Biju 33

Given Schedule: Serial Schedule of the given schedule:

T1 T2 As we know that in Serial schedule a transaction only
----- ------ starts when the current running transaction is finished.
R(X) So the serial schedule of the above given schedule would
W(X) look like this:
R(X)
T1 T2
W(X)
----- ------
R(Y)
R(X)
W(Y)
W(X)
R(Y)
R(Y)
W(Y)
W(Y)
R(X)
W(X)
R(Y)
W(Y)

If we can prove that the given schedule is View Equivalent to its serial
schedule then the given schedule is called view Serializable.
Prepared by Shimi Biju 34

17
 9/18/2019

Why we need View Serializability?

 We know that a serial schedule never leaves the database in

inconsistent state because there are no concurrent transactions
execution.
 However a non-serial schedule can leave the database in
inconsistent state because there are multiple transactions running
concurrently.
 By checking that a given non-serial schedule is view serializable,
we make sure that it is a consistent schedule.

Prepared by Shimi Biju 35

View Equivalent
 Two schedules T1 and T2 are said to be view equivalent, if they satisfy
all the following conditions:
1. Initial Read: Initial read of each data item in transactions must match
in both schedules. For example, if transaction T1 reads a data item X
before transaction T2 in schedule S1 then in schedule S2, T1 should
read X before T2.
Read vs Initial Read: Here initial read means the first read operation
on a data item, for example, a data item X can be read multiple times
in a schedule but the first read operation on X is called the initial read.
1. Final Write: Final write operations on each data item must match in
both the schedules. For example, a data item X is last written by
Transaction T1 in schedule S1 then in S2, the last write operation on X
should be performed by the transaction T1.
2. Update Read: If in schedule S1, the transaction T1 is reading a data
item updated by T2 then in schedule S2, T1 should read the value after
the write operation of T2 on same data item. For example, In schedule
S1, T1 performs a read operation on X after the write operation on X
by T2 then in S2, T1 should read the X after T2 performs write on X.
Prepared by Shimi Biju 36

18
 9/18/2019

View Serializable

 If a schedule is view equivalent to its serial schedule then the

given schedule is said to be View Serializable

Prepared by Shimi Biju 37

View Serializable Example

Prepared by Shimi Biju 38

19
 9/18/2019

 Lets check the three conditions of view serializability:

 Initial Read
 In schedule S1, transaction T1 first reads the data item X. In S2
also transaction T1 first reads the data item X.
 Lets check for Y. In schedule S1, transaction T1 first reads the
data item Y. In S2 also the first read operation on Y is performed
by T1.
 We checked for both data items X & Y and the initial
read condition is satisfied in S1 & S2.

Prepared by Shimi Biju 39

 Final Write
 In schedule S1, the final write operation on X is done by
transaction T2. In S2 also transaction T2 performs the final write
on X.
 Lets check for Y. In schedule S1, the final write operation on Y is
done by transaction T2. In schedule S2, final write on Y is done
by T2.
 We checked for both data items X & Y and the final
write condition is satisfied in S1 & S2.

Prepared by Shimi Biju 40

20
 9/18/2019

 Update Read
 In S1, transaction T2 reads the value of X, written by T1. In S2,
the same transaction T2 reads the X after it is written by T1.
 In S1, transaction T2 reads the value of Y, written by T1. In S2,
the same transaction T2 reads the value of Y after it is updated by
T1.
 The update read condition is also satisfied for both the schedules.

Prepared by Shimi Biju 41

 Result: Since all the three conditions that checks whether the two
schedules are view equivalent are satisfied in this example, which
means S1 and S2 are view equivalent.
 Also, as we know that the schedule S2 is the serial schedule of
S1, thus we can say that the schedule S1 is view serializable
schedule.

Prepared by Shimi Biju 42

21
 9/18/2019

Transaction Control

 The following commands are used to control transactions.

 COMMIT − to save the changes.
 ROLLBACK − to roll back the changes.
 SAVEPOINT − creates points within the groups of transactions
in which to ROLLBACK.
 SET TRANSACTION − Places a name on a transaction.

Prepared by Shimi Biju 43

Transactional Control Commands

 Transactional control commands are only used with the DML

Commands such as - INSERT, UPDATE and DELETE only.
 They cannot be used while creating tables or dropping them
because these operations are automatically committed in the
database.
 The COMMIT Command
 The COMMIT command is the transactional command used to
save changes invoked by a transaction to the database.
 The COMMIT command is the transactional command used to
save changes invoked by a transaction to the database.
 The COMMIT command saves all the transactions to the database
since the last COMMIT or ROLLBACK command.
Prepared by Shimi Biju 44

22
 9/18/2019

Example
 The syntax for the COMMIT command is as follows.
 COMMIT;

 Consider the CUSTOMERS table having the following records −

 +----+----------+-----+-----------+----------+
 | ID | NAME | AGE | ADDRESS | SALARY |
 +----+----------+-----+-----------+----------+
 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |
 +----+----------+-----+-----------+----------+
Prepared by Shimi Biju 45

 Following is an example which would delete those records from the table which
have age = 25 and then COMMIT the changes in the database.
 SQL> DELETE FROM CUSTOMERS WHERE AGE = 25;
 SQL> COMMIT;
 Thus, two rows from the table would be deleted and the SELECT statement would
produce the following result.

 +----+----------+-----+-----------+----------+
 | ID | NAME | AGE | ADDRESS | SALARY |
 +----+----------+-----+-----------+----------+
 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |
 +----+----------+-----+-----------+----------+

Prepared by Shimi Biju 46

23
 9/18/2019

The ROLLBACK Command

 The ROLLBACK command is the transactional command used to undo
transactions that have not already been saved to the database. This command can
only be used to undo transactions since the last COMMIT or ROLLBACK
command was issued.
 The syntax for a ROLLBACK command is as follows −
 ROLLBACK;

 Example
 Consider the CUSTOMERS table having the following records −
 | ID | NAME | AGE | ADDRESS | SALARY |
 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |
Prepared by Shimi Biju 47

 Following is an example, which would delete those records from

the table which have the age = 25 and then ROLLBACK the
changes in the database.

 SQL> DELETE FROM CUSTOMERS

 WHERE AGE = 25;
 SQL> ROLLBACK;

Prepared by Shimi Biju 48

24
 9/18/2019

 Thus, the delete operation would not impact the table and the SELECT statement
would produce the following result.

 +----+----------+-----+-----------+----------+
 | ID | NAME | AGE | ADDRESS | SALARY |
 +----+----------+-----+-----------+----------+
 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |
 +----+----------+-----+-----------+----------+

Prepared by Shimi Biju 49

The SAVEPOINT Command

 A SAVEPOINT is a point in a transaction when you can roll the

transaction back to a certain point without rolling back the entire
transaction.
 The syntax for a SAVEPOINT command is as shown below.
 SAVEPOINT SAVEPOINT_NAME;
 This command serves only in the creation of a SAVEPOINT
among all the transactional statements. The ROLLBACK
command is used to undo a group of transactions.

 The syntax for rolling back to a SAVEPOINT is as shown below.

 ROLLBACK TO SAVEPOINT_NAME;

Prepared by Shimi Biju 50

25
 9/18/2019

example
 Delete the three different records from the CUSTOMERS table. You want to
create a SAVEPOINT before each delete, so that you can ROLLBACK to any
SAVEPOINT at any time to return the appropriate data to its original state.
 Example
 Consider the CUSTOMERS table having the following records.

 | ID | NAME | AGE | ADDRESS | SALARY |

 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |

Prepared by Shimi Biju 51

 The following code block contains the series of operations.

 SQL> SAVEPOINT SP1;

 Savepoint created.
 SQL> DELETE FROM CUSTOMERS WHERE ID=1;
 1 row deleted.
 SQL> SAVEPOINT SP2;
 Savepoint created.
 SQL> DELETE FROM CUSTOMERS WHERE ID=2;
 1 row deleted.
 SQL> SAVEPOINT SP3;
 Savepoint created.
 SQL> DELETE FROM CUSTOMERS WHERE ID=3;
 1 row deleted.

Prepared by Shimi Biju 52

26
 9/18/2019

 Now that the three deletions have taken place, let us assume that you have decided to
ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after
the first deletion, the last two deletions are undone −
 SQL> ROLLBACK TO SP2;
 Rollback complete.
 Notice that only the first deletion took place since you rolled back to SP2.

 SQL> SELECT * FROM CUSTOMERS;

 | ID | NAME | AGE | ADDRESS | SALARY |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |
 6 rows selected.

Prepared by Shimi Biju 53

The RELEASE SAVEPOINT Command

 The RELEASE SAVEPOINT command is used to remove a

SAVEPOINT that you have created.

 The syntax for a RELEASE SAVEPOINT command is as

follows.

 RELEASE SAVEPOINT SAVEPOINT_NAME;

 Once a SAVEPOINT has been released, you can no longer use the
ROLLBACK command to undo transactions performed since the
last SAVEPOINT

Prepared by Shimi Biju 54

27
 9/18/2019

The SET TRANSACTION Command

 The SET TRANSACTION command can be used to initiate a

database transaction. This command is used to specify
characteristics for the transaction that follows. For example, you
can specify a transaction to be read only or read write.

 The syntax for a SET TRANSACTION command is as follows.

 SET TRANSACTION [ READ WRITE | READ ONLY ];

Prepared by Shimi Biju 55

28