DEV Community: Pham Duc Minh

Millions record query - with and without index

Pham Duc Minh — Fri, 24 Mar 2023 09:11:54 +0000

Just found out the movie page imdb database on imdb
It's a large dataset that contains Movie information use PosgresSQL

Quickly import the name_basics table, curious about what it looks like

A simple table with People's names and some other information

Count the table return 12.382.531 records, but what makes me surprise is table has no primary key or index

Wow, it will take a long time to search...

Right away try to search for my favorite actor Jean Reno, think about taking a coffee in the waiting time (find one in 12 million)

SELECT *
    FROM name_basics
WHERE "primaryName" = 'Jean Reno'
  AND "primaryProfession" = 'actor';

The result is another surprise

1 row retrieved starting from 1 in 923 ms (execution: 908 ms, fetching: 15 ms)

Is it because the table is simple? Or postgres has some cache?
Query a non-index text field in 12 million just take 1 second

What do we need index for? This is a really good performance

But I also want to see how performance go with index, so I add a simple index on primaryName field

create index "primaryName_index" on name_basics ("primaryName");

And re-try the select query

1 row retrieved starting from 1 in 34 ms (execution: 6 ms, fetching: 28 ms)

It's a huge different 900ms vs 6ms (for SQL execute), 150 times faster without index

Conclusion

The Index is magic! but it is not free, when query for index size

SELECT i.relname "Table Name",indexrelname "Index Name",
 pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
 pg_size_pretty(pg_relation_size(relid)) as "Table Size",
 pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
 reltuples::bigint "Estimated table row count"
 FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid
 WHERE i.relname='name_basics'

It takes ~ 400MB storage (a single-column index)
and also memory when processing.

Anyway, the performance it brings is no doubt.

[UPDATE]
After some research, I found some tips about performance

What is a good latency for API?
Generally, APIs that are considered high-performing have an average response time between 0.1 and one second. At this speed, end users will likely not experience any interruption. At around one to two seconds, users begin to notice some delay.

So take one second only for query database is a need to improve

My machine: Apple M1, Postgres 11 on docker

Pessimistic lock with JPA and Hibernate

Pham Duc Minh — Mon, 30 Jan 2023 04:17:14 +0000

On the previous article, I introduce about Optimistic Lock, this technic allow you to know that data has changed by someone else

But there's pros and cons

Pros

Easy to understand and implement
Completely control by Java/JPA, works across multiple database

Cons

Optimistic Lock let conflict happen
You need to implement what to do when conflict happens (re-try, cancel...)

Let's consider this use case:

I go to social media and ask for donate to help me buy a new Car. Because I was a good guy, so all of my friend start to transfer money to my bank account

That's good, but when too many people do it, race condition may happen to my bank account's database record

In this case, Optimistic Lock will throw an exception and tell users to re-try. I don't like it, because he/she may change his mind and won't transfer anymore

So Pessimistic Lock here to help

Pessimistic Lock in hibernate is a wrapper of database's lock technic

There's 3 Pessimistic Lock mode in org.hibernate.LockMode

PESSIMISTIC_READ	allows us to obtain a shared lock and prevent the data from being updated or deleted
PESSIMISTIC_WRITE	allows us to obtain an exclusive lock and prevent the data from being read, updated or deleted
PESSIMISTIC_FORCE_INCREMENT	works like PESSIMISTIC_WRITE, and it additionally increments a version attribute of a versioned entity

Code Example

package com.vominh.example.jpa;

import com.vominh.example.jpa.entity.DeviceWithVersionEntity;
import org.hibernate.LockMode;
import org.hibernate.LockOptions;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.concurrent.CompletableFuture;

public class TestPessimisticLock {
    private static final Logger log = LoggerFactory.getLogger(TestPessimisticLock.class);

    public static void main(String[] args) throws InterruptedException {
        DataUtils.setup();
        SessionFactory sessionFactory = AppSessionFactory.buildSessionFactory();

        CompletableFuture.runAsync(() -> {
            log.info("Thread 1, load and lock record");
            var session1 = sessionFactory.openSession();
            var lockOptions = new LockOptions(LockMode.PESSIMISTIC_READ);

            // Search and lock record, prevent update
            session1.getTransaction().begin();
            DeviceWithVersionEntity d1 = (DeviceWithVersionEntity) session1.load(DeviceWithVersionEntity.class, 1, lockOptions);
            try {
                // Delay close session1 to make session2 wait to commit
                Thread.sleep(10000);
                session1.getTransaction().commit();
                session1.close();
                log.info("Session 1 closed");
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }

            log.info("Thread 1, finished");
        });

        CompletableFuture.runAsync(() -> {
            try {
                Thread.sleep(3000);
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
            log.info("Thread 2, load and update record");
            // Try to update locking record
            var session2 = sessionFactory.openSession();
            DeviceWithVersionEntity d2 = (DeviceWithVersionEntity) session2.load(DeviceWithVersionEntity.class, 1);
            log.info("LockMode.PESSIMISTIC_READ still allow another session to read the record, devide's name: {}", d2.getName());
            try {
                d2.setName("new name");
                session2.getTransaction().begin();
                session2.update(d2);
                session2.getTransaction().commit(); // wait if session1 not close
                session2.close();
                log.info("Session 2 closed");
            } catch (Exception e) {
                log.info(e.getMessage());
                e.printStackTrace();
            }

            log.info("Thread 2, finished");
        });

        // Wait for thread 1,2 finish to view log
        Thread.sleep(20000);

        sessionFactory.close();
        log.info("EXIT");
    }
}

Log result

INFO com.vominh.example.jpa.TestPessimisticLock - Thread 1, load and lock record
INFO com.vominh.example.jpa.TestPessimisticLock - Thread 2, load and update record
INFO com.vominh.example.jpa.TestPessimisticLock - LockMode.PESSIMISTIC_READ still allow another session to read the record, devide's name: Dell xps 88
INFO com.vominh.example.jpa.TestPessimisticLock - Session 1 closed
INFO com.vominh.example.jpa.TestPessimisticLock - Thread 1, finished
INFO com.vominh.example.jpa.TestPessimisticLock - Session 2 closed
INFO com.vominh.example.jpa.TestPessimisticLock - Thread 2, finished

Scenario explain

Create two thread, thread 1 load a record and lock it with LockMode.PESSIMISTIC_READ then pause thread for 10s to keep the session opening (I want it available to read, but not allow to update/delete)

When Thread 2 load the locked record, it successfully read the information

But when update it, session 2 got block cause session 1 still open, the lock is not release yet

After 10s pause, session 1 closed then session 2 is able to commit the transaction

Apply this technic to my above use case, I can receive all transfer donate money without block any transaction

Conclusion

Pessimistic locking aims to avoid conflicts by using locking.
Pessimistic Lock is relay on the Database, and not all database use the same lock technic. In my example, I use postgres, when executing this sql in thread 1 pause time, I can see the query that gets locked in database
```
select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
```
Hibernate support add timeout to LockOptions by setTimeOut(int timeout) method
Hibernate support lock scope PessimisticLockScope.EXTENDED to block related entities in a join table

Full example project available at: Github

Multithreading example with video play by JavaFx

Pham Duc Minh — Mon, 30 May 2022 09:52:00 +0000

When start research about Java multithread, I saw an example how it should use:

when downloading a large file (e.g., an image, an audio clip or a video clip) over the Internet, the user may not want to wait until the entire clip downloads before starting the playback.

It just normal thing nowadays, like what Youtube did

So i start implement an Video Play app to get understand about it

1.Range HTTP request header

You want to download a portion of video file, your server need to support Range HTTP header

In this example, I store media file on AWS S3 server, which one supported Range header request

Sample file: Sting+-+Shape+of+My+Heart+(Leon).mp4

Here is sample Download task that extend javafx.concurrent.Task

Input URL and range define in String, output a byte[]

package com.vominh.example.thread.fx.task;

import javafx.concurrent.Task;

import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;

public class Download extends Task {

    private String url;
    private String range;

    public Download(String url, String range) {
        this.url = url;
        this.range = range;
    }

    @Override
    public byte[] call() throws Exception {
        URL publicUrl = new URL(url);
        HttpURLConnection conn = (HttpURLConnection) publicUrl.openConnection();
        conn.setRequestProperty("Range", "bytes=" + range);
        conn.connect();
        byte[] data;
        try (InputStream inputStream = conn.getInputStream()) {
            data = inputStream.readAllBytes();
        }

        return data;
    }
}

2.How to download and play at the sametime

2.1. Get content-length and calculate chunk size in byte

This simple snippet code use java.net.HttpURLConnection allow me to get file information without download it

URL url = new URL(urlString);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.getContentType();
conn.getContentLengthLong()

Depends on content-length, I calculate chunk size = contentLength/10. First download byte[] has range: 0-{chunk size}

Ex: a mp4 file with size = 20MB (20.000.000 Byte)
Chunk size = 20.000.000/10 = 2.000.000 Byte
First Range header: "bytes=0-2000000"
Next Range header: "bytes=2000001-4000000"
Next Range header: "bytes=4000001-6000000"

2.2. Create file from first download byte[]

When first part of file downloaded, I create a file to temp directory of Operation System

One more luxury feature is try to grab thumbnail from video file
There's a library from org.bytedeco support to do it.

2.3. When next download happen?

javafx.scene.media.MediaPlay support event listener every second when media file playing

MediaPlayer player = new MediaPlayer(media);
player.currentTimeProperty().addListener(observable -> {
    // Event fire every 1/4 second
    int currentSecond = (int) player.getCurrentTime().toSeconds();
});

Base on this, I submit Download task when played amount = x percent of downloaded
x should < 70% of total downloaded to make video play without pause.
This step is quite complex cause it depends on file size, internet speed... And it hard to optimize for large file

When new part downloaded, I just append byte[] to the temp file created on first step

3.Thread information

I put some log in applications to see how many threads were created and what it is, and here is result

Main thread -> created automatically when our program is started, your Java code execute by this thread
JavaFX Application Thread -> Thread to perform GUI tasks
pool-2-thread-1 -> Thread open to download data

4. Remaining issues of the app

Can not resume play when progress rearch the end of downloaded data (network error or download point not optimize)
Seed not support
MediaPlay not fully support media type, for example I could not play .mkv file. See Supported media type here Source is available at my Github repo

Prevent database concurrency with @Version JPA and Hibernate

Pham Duc Minh — Sun, 29 May 2022 14:20:05 +0000

When working on a project of big business company, sometime someone will ask "What happens when 2 users update the same record in the database?"

It sound confuse for the new developer, but very easy to avoid it.

In Java Persistence API (JPA), there's @version annotation that will help you to check how many time the database's record has been updated

Let's have a look at these simple table and entity

create table device
(
    id      integer not null constraint device_pk primary key,
    serial  integer,
    name    varchar(255),
    version integer
)

And the entity

package com.vominh.example.entity;

import javax.persistence.*;

@Entity
@Table(name = "device")
public class DeviceWithVersionEntity {
    @Id
    @Column(name = "id")
    private Integer id;
    @Column(name = "serial")
    private Integer serial;
    @Column(name = "name")
    private String name;
    @Version
    @Column(name = "version")
    private int version;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getSerial() {
        return serial;
    }
    public void setSerial(Integer serial) {
        this.serial = serial;
    }

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

    public int getVersion() {
        return version;
    }

    public void setVersion(int version) {
        this.version = version;
    }
}

The part can understand like "This field will increase by 1 when someone perform update action on it"

@Version
@Column(name = "version")
private int version;

Main class

package com.vominh.example;

import com.vominh.example.entity.DeviceWithVersionEntity;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;

import java.util.Random;
import java.util.concurrent.CompletableFuture;

public class ConcurrencyControl {
    private static final SessionFactory sessionFactory;
    private static final ServiceRegistry serviceRegistry;

    static {
        Configuration configuration = new Configuration();
        configuration.configure("hibernate.cfg.xml");

        // Since Hibernate Version 4.x, ServiceRegistry Is Being Used
        serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
        sessionFactory = configuration.buildSessionFactory(serviceRegistry);
    }

    public static void main(String[] args) {
        Session session = sessionFactory.openSession();
        Query deleteAllDevice = session.createQuery("delete from DeviceWithVersionEntity");

        try {
            session.beginTransaction();
            deleteAllDevice.executeUpdate();

            DeviceWithVersionEntity device = new DeviceWithVersionEntity();
            device.setId(1);
            device.setSerial(8888);
            device.setName("Dell xps 99");

            session.save(device);

            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        } finally {
            session.close();
        }

        // open 50 session in 50 thread to update one record
        for (int i = 0; i < 50; i++) {
            CompletableFuture.runAsync(() -> {
                var s = sessionFactory.openSession();
                try {
                    s.beginTransaction();

                    DeviceWithVersionEntity d = (DeviceWithVersionEntity) s.load(DeviceWithVersionEntity.class, 1);
                    d.setName((new Random()).nextInt(500) + "");
                    s.save(d);

                    s.getTransaction().commit();
                } catch (Exception e) {
                    e.printStackTrace();
                    s.getTransaction().rollback();
                } finally {
                    s.close();
                }
            });
        }
    }
}

First part save a record to database
Second part create 50 thread & hibernate session then try to update the inserted record

Execute result will throw a lot of org.hibernate.StaleObjectStateException

The exception message already explain what happens, but how and when?

Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

If @version add to a field of Entity (number), when the create/update operation execute, Hibernate will set/increase value of @version field.

UPDATE device set name = 'new name', version = version + 1

When the next UPDATE happens, Hibernate will check if the version is matched by add a condition to WHERE clause

UPDATE device SET name = 'new name', version = version + 1
WHERE id = ? AND version = **CURRENT_VERSION**

if another session already update the record and version had increased, WHERE clause won't match and exception throw

This method also called Optimistic Locking and it's easy to implement with JPA and Hibernate (which handle the heavy logic)

Source code available at: https://github.com/docvominh/java-example/tree/master/hibernate-jpa
I use maven, hibernate 4.3 and postgres sql

Thank you for your reading!

5 years working with Java, got big surprise today with "Pass by value"

Pham Duc Minh — Sun, 01 May 2022 14:54:13 +0000

Today I saw a simple Java exam question about OOP

Before it, I knew stack and heap and the Java rule everything pass by value.

And value means the reference address stores in stack, not the object in heap.

Let have a look at this question:
What is output of this execute code?

class Person {
    private String name;
    public Person(String name) {this.name = name;}
    public String toString() {return name;}
}


public class Tester {

    public static Person checkPerson(Person p) {
        if (p == null) {
            p = new Person("Joe");
        } else {
            p = null;
        }
        return p;
    }

    public static void main(String[] args) {
        Person p = null;
        checkPerson(p);
        System.out.print(p);
        p = new Person("Mary");
        checkPerson(p);
        System.out.print(p);
    }
}

Answer
A) Joenull
B) nullnull
C) Marynull
D) nullMary

The checkPeson() method logic

if (p == null) {
    p = new Person("Joe");
} else {
    p = null;
}

ah ha, the first print must be "Joe" cause we pass a null to it
right away select the Answer A - Joenull without any hesitate, but correct answer is D) nullMary

Wait, what? the first print is null.
But we have p = new Person("Joe") already in the method?

I start to doubt about did Java pass the reference of object to the method?

After some crazy search on internet, I finally understand why

By simple value in "everything pass by value" mean a copy of reference address value (not the real reference value)

The parameter I pass to the method is a clone of the real one, it's a local variable (a variable that's declared within the body of a method)

The real one still points to a null value, it got no effect after the method execute

That why we have correct answer nullMary

Oh what a shame, after 7 years in Software Development and 5 years with Java, Sometime thing I am a senior but everything collapse after this.

Couldn't believe I lacked this important point

My excuse

I have learn many languages whenever my company have a new project (or when I land new job). Learn Java at university, first job with C#, then Ruby, VBA and final comeback with Java
As a fullstack developer, I use 50% time working with Style and client events (javascript, JQuery)
Lazy reading technical document, always prefer short explain and blog cause all I want is make code run as soon as possible

The way I am working is still good, customers love that problem solve quickly and I'm like a superstar in my team

But for me, it's time to slow down, learn the concept instead of google for code and use Trial-error method