In a multiuser environment same row/record may be accessed/updated concurrently
This could cause data inconsistency and anomaly.
Locking as such is a wide concept and can be implemented in many ways.
It differs according to the DBMS, language and complexity of the project.
by more than one user.
To avoid such situations and maintain data integrity, locking concept is applied.
In this example program,
record locking is done by having a separate column for LOCK status in the table.
This is done through program logic.
A row is retrieved for UPDATE, only if the LOCK column is marked as FALSE.
If LOCK status is FALSE, the program marks the LOCK status TRUE, writes it to
the database and allows the user to make changes.
After the user update, it writes the UPDATED row and changes LOCK
status to FALSE again.
So that it can be accessed by other users.
Software used is NetBeans IDE 8.0.2.
Note : ClientId is unique and do not allow duplicates.
Database is created in JavaDB which is part of JDK package.Database ClientDB and table Mailist and records are created through the wizard.Programs also created using Netbeans IDE 8.0.2.Same code holds good for other DBMS with the corresponding driver program.
Database Name : ClientDB
Table Name : Maillist
Table Attributes : ClientId N
ClientName A/N
ClientEmail A/N
Lock Boolean
EXAMPLE -
package lockbycolumn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class LockByColumn {
String host = "jdbc:derby://localhost:1527/clientDB";
String UserName = "parthiban";
String UserPassword = "password";
String sql;
Connection con;
ResultSet rs;
PreparedStatement ps;
Statement ss;
Scanner AcceptInput = new Scanner(System.in);
int WClientId; // Work Variables
String WClientName, WClientEmail,Wlock;
public static void main(String[] args) {
// Cmo is created to enable non static method calling from static Main
LockByColumn Cmo = new LockByColumn();
Scanner AcceptInput = new Scanner(System.in);
String OptionInput;
Cmo.ConnectMethod();
do {
System.out.println( " ** Select an Operation **");
System.out.println( " Retrieval of Record - 1");
System.out.println( " Update of Record - 2");
System.out.println( " Exit - 0");
System.out.println( " ENTER OPTION - ");
OptionInput = AcceptInput.next();
switch (OptionInput) {
case "1":
Cmo.RetrieveRecord();
break;
case "2":
Cmo.UpdateRecord();
break;
case "0":
// For input 0, program is terminated
System.out.println("Ending Session");
System.exit(0);
break;
default:
System.out.println("Invalid selection - Reenter");
break;
} // end switch
// Note, how string value is checked for not = "0"
} while (!OptionInput.equals("0"));
}// end main
/* CONNECTION METHOD */
public void ConnectMethod(){
try{
con = DriverManager.getConnection(host,UserName,UserPassword);
System.out.println(" Connected..");
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
/* RECORD RETIEVAL METHOD */
/* this method lists rows with UNDER UPDATE message if that row is under
process by another user */
public void RetrieveRecord(){
System.out.println("RECORD RETRIEVAL");
WClientId =1;
try {
sql ="SELECT * from MAILLIST ";
ps = con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
WClientId = rs.getInt(1);
WClientName = rs.getString(2);
WClientEmail = rs.getString(3);
Wlock=" ";
/* If LOCK column is TRUE,then Uner Update message is displayed */
if (rs.getBoolean(4)){
Wlock = " -- Under UPDATE --";
}
System.out.println("Client Id : " + WClientId + ", Name : "
+ WClientName + ", EMail : " + WClientEmail
+ Wlock);
}
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
/* RECORD UPDATE METHOD */
public void UpdateRecord(){
System.out.println("RECORD UPDATE");
WClientId = 1;
try {
while (WClientId != 0) {
System.out.println("Enter Client Id (0 to EXIT): ");
WClientId = AcceptInput.nextInt();
AcceptInput.nextLine();// to avoid skipping next input
if (WClientId !=0){
sql ="SELECT Clientname,clientemail,lock from MAILLIST where CLIENTID = ? ";
ps = con.prepareStatement(sql);
ps.setInt(1, WClientId);
rs=ps.executeQuery();
rs.next();
WClientName = rs.getString(1);
WClientEmail = rs.getString(2);
Wlock ="";
/* if LOCK column is TRUE display RECORD is locked message */
if (rs.getBoolean(3)){
System.out.println( "Record is Locked");
}
/* If RECORD is not locked, update LOCK column as TRUE
and proceed with update */
else {
sql ="UPDATE MAILLIST set lock = ? where ClientId = ? ";
ps = con.prepareStatement(sql);
ps.setInt(2, WClientId);
ps.setBoolean(1,true);
ps.executeUpdate();
System.out.println("Client Id : " + WClientId + ", Name : "
+ WClientName + ", Current EMail : " + WClientEmail);
System.out.println("Enter New Email or press ENTER for no chnage):");
WClientEmail = AcceptInput.nextLine();// New Email
sql ="UPDATE MAILLIST SET ClientEmail = ? ,lock = ? where ClientId = ? ";
ps = con.prepareStatement(sql);
ps.setString(1,WClientEmail);
/* Reset LOCK column as FALSE as part of update */
ps.setBoolean(2,false );
ps.setInt(3, WClientId);
ps.executeUpdate();
}
}
}
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
}
