Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

Assignment No:9

Title: Transaction Implementation and Concurrency Control

Problem Statement: Simulate a bank transfer between two accounts using transactions.

Course Objective: To become familiar with transactions, concurrency, and query optimization.

Course Outcome: Demonstrate transaction properties, concurrency control, and query

optimization methods for efficient database performance.

Tools Required:.Java, Eclipse, MySql

Theory:

1. Transaction processing:
 Transaction in Database Management Systems (DBMS) can be defined as a set of
logically related operations.
 It is the result of a request made by the user to access the contents of the database and
perform operations on it.
 It consists of various operations and has various states in its completion journey.
 It also has some specific properties that must be followed to keep the database consistent.
1.1 Operations of Transaction
A user can make different types of requests to access and modify the contents of a database. So,
we have different types of operations relating to a transaction. They are discussed as follows:
 Read(X): A read operation is used to read the value of X from the database and store it in
a buffer in the main memory for further actions such as displaying that value.
 Write(X): A write operation is used to write the value to the database from the buffer in
the main memory. For a write operation to be performed, first a read operation is
performed to bring its value in buffer, and then some changes are made to it, e.g. some set
of arithmetic operations are performed on it according to the user’s request, then to store
the modified value back in the database, a write operation is performed.
 Commit: This operation in transactions is used to maintain integrity in the database. Due
to some failure of power, hardware, or software, etc., a transaction might get interrupted
before all its operations are completed.

2. Property of Transaction
2.1. Atomicity
It states that all operations of the transaction take place at once if not, the transaction is aborted.
There is no midway, i.e., the transaction cannot occur partially. Each transaction is treated as one
unit and either run to completion or is not executed at all.
Atomicity involves the following two operations:
Abort: If a transaction aborts then all the changes made are not visible.
Commit: If a transaction commits then all the changes made are visible.

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 1

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

Example: Let's assume that following transaction T consisting of T1 and T2. A consists of Rs
600 and B consists of Rs 300. Transfer Rs 100 from account A to account B.

After completion of the transaction, A consists of Rs 500 and B consists of Rs 400.

If the transaction T fails after the completion of transaction T1 but before completion of
transaction T2, then the amount will be deducted from A but not added to B. This shows the
inconsistent database state. In order to ensure correctness of database state, the transaction must
be executed in entirety.
2.2. Consistency
A transaction must bring the database from one valid state to another valid state.
It ensures all integrity constraints, rules, and relationships are preserved.
Example: After a transfer, the total balance across all accounts must remain unchanged.
2.3. Isolation
Multiple transactions can occur concurrently, but they should not interfere with each other.
Each transaction must behave as if it is executed alone in the system.
Example: If two users transfer money at the same time, their transactions should not overwrite or
corrupt each other’s data.
2.4. Durability
Once a transaction is committed, the changes are permanent in the database, even in case of
system failures (e.g., power loss or crash).
Example: Once money is successfully transferred, the record remains saved even if the system
shuts down immediately after.

3. Schedule
 A series of operation from one transaction to another transaction is known as schedule.
 It is used to preserve the order of the operation in each of the individual transaction.
3.1 Serial Schedule
 The serial schedule is a type of schedule where one transaction is executed completely
before starting another transaction.

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 2

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

 In the serial schedule, when the first transaction completes its cycle, then the next
transaction is executed.
For example: Suppose there are two transactions T1 and T2 which have some operations. If
it has no interleaving of operations, then there are the following two possible outcomes:
 Execute all the operations of T1 which was followed by all the operations of T2.
 Execute all the operations of T1 which was followed by all the operations of T2.
 In the given (a) figure, Schedule A shows the serial schedule where T1 followed by T2.
 In the given (b) figure, Schedule B shows the serial schedule where T2 followed by T1.

3.2 Non-serial Schedule

 If interleaving of operations is allowed, then there will be non-serial schedule.
 It contains many possible orders in which the system can execute the individual
operations of the transactions.
 In the given figure (c) and (d), Schedule C and Schedule D are the non-serial schedules. It
has interleaving of operations.
3.3 Serializable schedule
 The serializability of schedules is used to find non-serial schedules that allow the
transaction to execute concurrently without interfering with one another.
 It identifies which schedules are correct when executions of the transaction have
interleaving of their operations.
 A non-serial schedule will be serializable if its result is equal to the result of its
transactions executed serially.

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 3

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

Suppose the current values of accounts A and B are $1000 and $2000, respectively. Suppose also
that the two transactions are executed one at a time in the order T1 followed by T2. The final
values of accounts A and B, after the execution in schedule 1 takes place, are $855 and $2145,
respectively. Thus, the total amount of money in accounts A and B— that is, the sum A + B—is
preserved after the execution of both transactions
Similarly, if the transactions are executed one at a time in the order T2 followed by T1, then the
corresponding execution sequence is that of Figure 2. Again, as expected, the sum A + B is
preserved, and the final values of accounts A and B are $850 and $2150, respectively. The
execution sequences just described are called schedules.
Suppose that the two transactions are executed concurrently shown in Figure 3. After this
execution takes place, we arrive at the same state as the one in which the transactions are
executed serially in the order T1 followed by T2. The sum A + B is indeed preserved

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 4

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

We can ensure consistency of the database under concurrent execution by making sure that any
schedule that is executed has the same effect as a schedule that could have occurred without any
concurrent execution. That is, the schedule should, in some sense, be equivalent to a serial
schedule. Such schedules are called serializable schedules.

4. Concurrency Control Techniques

4.1 Lock-Based Protocols
 One way to ensure isolation is to require that data items be accessed in a mutually
exclusive manner; that is, while one transaction is accessing a data item, no other
transaction can modify that data item.
 The most common method used to implement this requirement is to allow a transaction to
access a data item only if it is currently holding a lock on that item.

4.1.1 Locks
 There are various modes in which a data item may be locked.
1. Shared. If a transaction Ti has obtained a shared-mode lock (denoted by S) on item Q, then Ti
can read, but cannot write, Q.
2. Exclusive. If a transaction Ti has obtained an exclusive-mode lock (denoted by X) on item Q,
then Ti can both read and write Q.
We require that every transaction request a lock in an appropriate mode on data item Q,
depending on the types of operations that it will perform on Q. The transaction makes the request
to the concurrency-control manager. The transaction can proceed with the operation only after
the concurrency-control manager grants the lock to the transaction. The use of these two lock
modes allows multiple transactions to read a data item but limits write access to just one
transaction at a time.

4.2 The Two-Phase Locking Protocol

One protocol that ensures serializability is the two-phase locking protocol.
This protocol requires that each transaction issue lock and unlock requests in two phases:
1. Growing phase. A transaction may obtain locks, but may not release any lock.
2. Shrinking phase. A transaction may release locks, but may not obtain any new locks.
Initially, a transaction is in the growing phase. The transaction acquires locks as needed. Once
the transaction releases a lock, it enters the shrinking phase, and it can issue no more lock
requests.

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 5

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

 The point in the schedule where the transaction has obtained its final lock (the end of its
growing phase) is called the lock point of the transaction. Now, transactions can be
ordered according to their lock points—
 this ordering is, in fact, a serializability ordering for the transactions.

4.3 Timestamp-Based Protocols

 Timestamps With each transaction Ti in the system, we associate a unique fixed
timestamp, denoted by TS(Ti).
 This timestamp is assigned by the database system before the transaction Ti starts
execution. If a transaction Ti has been assigned timestamp TS(Ti), and a new transaction
Tj enters the system, then TS(Ti) < TS(Tj).
 There are two simple methods for implementing this scheme:
1. Use the value of the system clock as the timestamp; that is, a transaction’s timestamp is
equal to the value of the clock when the transaction enters the system.
2. Use a logical counter that is incremented after a new timestamp has been assigned; that
is, a transaction’s timestamp is equal to the value of the counter when the transaction
enters the system.
 The timestamps of the transactions determine the serializability order.
 Thus, if TS(Ti) < TS(Tj), then the system must ensure that the produced schedule is
equivalent to a serial schedule in which transaction Ti appears before transaction Tj .
 To implement this scheme, we associate with each data item Q two timestamp values:
• W-timestamp(Q) denotes the largest timestamp of any transaction that executed write(Q)
successfully.
• R-timestamp(Q) denotes the largest timestamp of any transaction that executed read(Q)
successfully.

4.3.1 The Timestamp-Ordering Protocol

The timestamp-ordering protocol ensures that any conflicting read and write operations are
executed in timestamp order. This protocol operates as follows:
1. Suppose that transaction Ti issues read(Q).
• If TS(Ti) < W-timestamp(Q), then Ti needs to read a value of Q that was already
overwritten. Hence, the read operation is rejected, and Ti is rolled back.
• If TS(Ti) ≥ W-timestamp(Q), then the read operation is executed, and R-timestamp(Q) is
set to the maximum of R-timestamp(Q) and TS(Ti).
2. Suppose that transaction Ti issues write(Q).
• If TS(Ti) < R-timestamp(Q), then the value of Q that Ti is producing was needed
previously, and the system assumed that that value would never be produced. Hence, the
system rejects the write operation and rolls Ti back.
• If TS(Ti) < W-timestamp(Q), then Ti is attempting to write an obsolete value of Q.
Hence, the system rejects this write operation and rolls Ti back. c. Otherwise, the system
executes the write operation and sets W-timestamp(Q) to TS(Ti).

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 6

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

4.4 Multiversion Timestamp Ordering

 The timestamp-ordering protocol can be extended to a multiversion protocol.
 With each transaction Ti in the system, we associate a unique static timestamp, denoted
by TS(Ti).
 The database system assigns this timestamp before the transaction starts execution.
 With each data item Q, a sequence of versions is associated.
 Each version Qk contains three data fields:
 Content is the value of version Qk .
 W-timestamp(Qk ) is the timestamp of the transaction that created version Qk .
 R-timestamp(Qk ) is the largest timestamp of any transaction that successfully read
version Qk .
 A transaction—say, Ti—creates a new version Qk of data item Q by issuing a write(Q)
operation.
 The content field of the version holds the value written by Ti .
 The system initializes the W-timestamp and R-timestamp to TS(Ti).
 It updates the R-timestamp value of Qk whenever a transaction Tj reads the content of Qk
, and R-timestamp(Qk ) < TS(Tj).
 The multiversion timestamp-ordering scheme presented next ensures serializability.
 The scheme operates as follows: Suppose that transaction Ti issues a read(Q) or write(Q)
operation.
 Let Qk denote the version of Q whose write timestamp is the largest write timestamp less
than or equal to TS(Ti).

4.5 Optimistic Concurrency Control schemes

 The optimistic approach is based on the assumption that the majority of the database
operations do not conflict.
 The optimistic approach requires neither locking nor time stamping techniques.
 Instead, a transaction is executed without restrictions until it is committed.
 Using an optimistic approach, each transaction moves through 2 or 3 phases, referred to
as read, validation and write.
1. (i) During read phase, the transaction reads the database, executes the needed
computations and makes the updates to a private copy of the database values. All update
operations of the transactions are recorded in a temporary update file, which is not
accessed by the remaining transactions.
2. (ii) During the validation phase, the transaction is validated to ensure that the changes
made will not affect the integrity and consistency of the database. If the validation test is
positive, the transaction goes to a write phase. If the validation test is negative, he
transaction is restarted and the changes are discarded.
3. (iii) During the write phase, the changes are permanently applied to the database.

Conclusion: Hence, we have successfully simulate a bank transfer between two accounts using
transactions.

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 7

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

Output:
package [Link];

/*
* CREATE TABLE bank_account (
-> id INT PRIMARY KEY,
-> name VARCHAR(50),
-> balance DOUBLE
-> );
INSERT INTO bank_account (id, name, balance) VALUES (1, 'nilesh',1000.00), (2,'keshav',
500.00),(3,'sunil',300);
*/
import [Link].*;
class Bank
{
private final String url = "jdbc:mysql://localhost:3309/test";
private final String user = "root";
private final String password = "root";

// Show current balance of all accounts

public void showBalances(String message)
{
[Link]("\n=== " + message + " ===");
try (Connection conn = [Link](url, user, password);
Statement stmt = [Link]();
ResultSet rs = [Link]("SELECT id, name, balance FROM bank_account
ORDER BY id"))
{

while ([Link]())
{
[Link]("Account " + [Link]("name") +
" (ID " + [Link]("id") + ") Balance: ₹" + [Link]("balance"));
}
}
catch (SQLException e)
{
[Link]();
}
}

// Synchronized transfer method with transaction

public synchronized void transfer(int fromId, int toId, double amount)
{
Connection conn = null;
try
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 8
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

{
conn = [Link](url, user, password);
[Link](false); // Start transaction

// Lock accounts and read balances

double fromBal = 0, toBal = 0;

Statement stmt1 = [Link]();

ResultSet rs1 = [Link]("SELECT balance FROM bank_account WHERE
id = " + fromId + " FOR UPDATE");
if ([Link]())
fromBal = [Link]("balance");
[Link]();
[Link]();

Statement stmt2 = [Link]();

ResultSet rs2 = [Link]("SELECT balance FROM bank_account WHERE
id = " + toId + " FOR UPDATE");
if ([Link]())
toBal = [Link]("balance");
[Link]();
[Link]();

if (fromBal >= amount)

{
fromBal -= amount;
toBal += amount;

Statement stmt3 = [Link]();

[Link]("UPDATE bank_account SET balance = " + fromBal + "
WHERE id = " + fromId);
[Link]("UPDATE bank_account SET balance = " + toBal + "
WHERE id = " + toId);
[Link]();

[Link]("Transferred ₹" + amount + " from ID " + fromId + " to ID " +

toId);
[Link]();
}
else
{
[Link]("Insufficient balance in ID " + fromId);
[Link]();
}

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 9

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

catch (Exception e)
{
[Link]();
try
{
if (conn != null) [Link]();
}
catch (SQLException se)
{
[Link]();
}
}
finally
{
try
{
if (conn != null) [Link]();
}
catch (SQLException se)
{
[Link]();
}
}
}
}

class BankThread extends Thread

{
Bank bank;
int fromId, toId;
double amount;

public BankThread(Bank bank, int fromId, int toId, double amount)

{
[Link] = bank;
[Link] = fromId;
[Link] = toId;
[Link] = amount;
}

public void run()

{
[Link](fromId, toId, amount);
}
}

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 10

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

public class BankTransferMain

{
public static void main(String[] args)
{
Bank bank = new Bank();

// Show initial balances

[Link]("Initial Balances");

// Run transfers in threads

BankThread t1 = new BankThread(bank, 1, 2, 500); // A → B
BankThread t2 = new BankThread(bank, 2, 3, 300); // B → C

[Link]();
[Link]();

try
{
[Link]();
[Link]();
}
catch (InterruptedException e)
{
[Link]();
}

// Show final balances

[Link]("Final Balances");
}
}
Output:
=== Initial Balances ===
Account nilesh (ID 1) Balance: ₹3300.0
Account keshav (ID 2) Balance: ₹300.0
Account sunil (ID 3) Balance: ₹1500.0
Account mohan (ID 4) Balance: ₹99999.0
Transferred ₹500.0 from ID 1 to ID 2
Transferred ₹300.0 from ID 2 to ID 3

=== Final Balances ===

Account nilesh (ID 1) Balance: ₹2800.0
Account keshav (ID 2) Balance: ₹500.0
Account sunil (ID 3) Balance: ₹1800.0
Account mohan (ID 4) Balance: ₹99999.0
Database:

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 11

Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01

Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 12