Tuesday, February 23, 2016

How to minimize the performance impact of locking in database

Database locks work as a solution to the problems associated with concurrency, consistency and integrity. But it comes with its own drawback of performance hit. In this article, we will talk about steps, we could take during database design to reduce the performance impact of locking.
Introduce optimum indexes - Introducing optimum indexes improves the performance impact of locking caused by those update and delete statements, which have got filter criteria associated with them. When a update or delete query with filter criteria is fired in database, it initially takes a lock on complete table to identify the records getting impacted. Once the records are identified, Sybase can take the lock on the selected rows, depending on the locking level set on the table . Implementing the index, would minimize the time required in identifying rows to be updated or deleted, which in turn would reduce the all page level locking time.
Choose the optimum locking level on server- Sybase ASE provide option to set the default locking scheme to be applied on newly created tables.It can be set as "allpages", "datapages" or "datarows "level. In "allpages" lock level, all the pages of table gets locked during any DML operation. In "datapages" lock level only the pages, which contains impacted data, gets locked. In "datarows " lock level, only the rows impacted with DML operations,gets locked.
The default locking scheme can be checked by firing following query -
 sp_configure "lock scheme"
Default locking scheme can be changed by following query - 
sp_configure "lock scheme", 0,  {allpages | datapages | datarows}
Choose optimum lock level on tables -It might not always be possible to set least restrictive locking scheme on the database server level. For these cases, Sybase ASE provide option to set locking level on table level. This locking level can be set during table creation as a part of create statement or changed later via alter statement.
Option to set locking level in create table statement -
create table table_name (column_name_list)[lock {datarows | datapages | allpages}]

Option to set locking level in alter table statement -
alter table table_name lock {allpages | datapages | datarows}
Keeping the transaction duration to minimum- locks are taken as a part of transaction and released at the end of transaction. Keeping the transaction duration sort helps us minimizing the lock duration and its impact on performance.
Partition the table- In a non-partitioned table, with no clustered index, all the inserts happens on the last page of the table. In case of concurrent inserts, it would create performance hit as every transaction would cause lock on the last page and let another transaction wait until the completion. In case of partitioned table, there would be multiple last pages causing less impact of locking.
Creating the clustered index-Clustered index provides multiple last page insertion points, which helps in reducing the impact of locks in concurrent inserts. But it also introduces an overhead of ordering, which might again have an impact on performance.
I hope, this article would have helped you in understanding the way, we could minimize the impact of locking in our application. Let me know your experience as well, if this article could help you some way or you could find some other ways as well to minimize the performance impact of locking.

Saturday, February 20, 2016

Testing method arguments using JUnit and Mockito ArgumentCaptor with example

Multiple times, we get to a situation, where we have a  public void method in our program, whose behaviour needs to be tested using  Junit.  Mostly such void methods, take some arguments to perform some operation locally and then pass objects for remote processing.

Automated testing of such scenarios can be done in one of following two approaches. - 
  1. Write a unit test and verify that our public method is performing necessary operations accurately and sending correct object to remote resources.
  2. Write an integration test by including third party module as well and then verify result from the remote resource.

In this article, we will be focus on first approach, where we write a JUnit test to ensure that our method is performing local operations accurately and sending the right object with right values to remote resource.

we can write such test cases using Mockito framework and its Argument captor API. Argument API provides us methods  like capture, getValue and getAllValues to capture the values passed by the method under test to mock of remote resource  and retrieve those values for verification.

In this Mockito Agrument captor example, we have a class MailProcessor, which takes the employee object from database on the basis of id, transform it to dto object  and pass it to mailing service for sending mail.

Technology used -
java version "1.7.0_06"
Apache Maven 3.0.4
junit -4.12
mockito -1.10.19

pom.xml -

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

Project Structure


package salil.mockito.argumentcaptorexample;

public class Employee {

 private int employeeId;
 private String name;
 private String email;
 private int salarry;

 public int getEmployeeId() {
  return employeeId;

 public void setEmployeeId(int employeeId) {
  this.employeeId = employeeId;

 public String getName() {
  return name;

 public void setName(String name) {
  this.name = name;

 public String getEmail() {
  return email;

 public void setEmail(String email) {
  this.email = email;

 public int getSalarry() {
  return salarry;

 public void setSalarry(int salarry) {
  this.salarry = salarry;



package salil.mockito.argumentcaptorexample;

public interface EmployeeDao {

 Employee getEmployee(int employeeId);


package com.salil.argumentCaptor.domain;

public class EmpMailDTO {

 private final String reciepientEmail;
 private final String sender="admin@salilstock.blogspot.com";
 private final String subject="Salary notification";
 private final static String templateMessage="Dear %s\n\n we are processing your salary amount %s. \n\n Regards\nAdmin";
 private final String message;
 public EmpMailDTO(final String name,final String reciepientEmail,final int salary)
  this.message=String.format(templateMessage, name,salary);
 public String getReciepientEmail() {
  return reciepientEmail;

 public String getSender() {
  return sender;
 public String getMessage() {
  return message;

 public String getSubject() {
  return subject;


package com.salil.argumentCaptor;

import com.salil.argumentCaptor.domain.EmpMailDTO;

public interface MailingService {
 void sendEmail(EmpMailDTO employeeMail);

Method to be tested - MailProcessor.sendEmailToEmployee -
package salil.mockito.argumentcaptorexample;

public class MailProcessor {

 private final EmployeeDao employeeDao;
 private final MailingService mailingService;
 public MailProcessor(EmployeeDao employeeDao, MailingService mailer) {
  this.employeeDao = employeeDao;
  this.mailingService = mailer;
 public void sendEmailToEmployee(int employeeId)
  Employee employee = employeeDao.getEmployee(employeeId);
  EmpMailDTO empMailDTO = new EmpMailDTO(employee.getName(), employee.getEmail(), employee.getSalarry());
 private boolean isEmployeeMailValid(String email)
  return email!=null;

Corresponding test case would be as following -

package salil.mockito.argumentcaptorexample;

import static org.junit.Assert.*;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.ArgumentCaptor;
import org.mockito.Mock;
import org.mockito.Mockito;
import org.mockito.runners.MockitoJUnitRunner;

public class MailProcessorTest {

 private EmployeeDao employeeDao;
 private MailingService mailingService;
 private int employeeId=1;
 private String name="Salil";
 private String email="admin@salilstock.blogspot.com";
 private int salarry=5000;
 private Employee employee;
 public void setup() {
  employee = new Employee();
 public void testSendEmailToEmployee() {
  MailProcessor mailProcessor = new MailProcessor(employeeDao, mailingService);
//  Instantiate argument captor to capture the argument of EmployeeMail type  
  ArgumentCaptorargumentCaptor =ArgumentCaptor.forClass(EmpMailDTO.class);
//  Perform operation
//  Capture the argument
//  verify the correctness of the receiving object
  EmpMailDTO empMailDTO=argumentCaptor.getValue();
  assertEquals(empMailDTO.getReciepientEmail(), this.email);
  assertEquals(empMailDTO.getMessage(),"Dear Salil\n\n we are processing your salary amount 5000. \n\n Regards\nAdmin");


This complete project can be accessed from the following git hub location.

Please let us know whether you found this article useful and your experience on using argument captor in your project.