This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL 9.5 introduced one of the powerful feature called SKIP LOCKED which is used for SKIP uncommitted data.
I am not going to make this more complex topic, explaining by one practical example.
In our project we have implemented thread mechanism. Different threads are running and taking a fixed set of records from the Database.
These threads basically implemented for performing Reverse Geo Coding on the stored Latitude and Longitude.
I am giving 10000 records to each thread in each batch.
Now the average execution time of each thread is 5 second to 7 second.
Now, one batch of 10000 records taken by thread_1 for 5 second to 7 second that same records should not taken by any other thread because that first 10000 records already under the process by thead_1.
Thread_2 has taken next 10000 records which are not taken by thread_1.
Now with the PostgreSQL 9.5, we do not require any additional locking mechanism or isolation level to achieve this scenario.
We can very easily achieve using FOR UPDATE SKIP LOCKED option. When we SELECT data using this option, it will skip all running transactions (uncommitted transaction) and return only required records to be processed.
Below is a full demonstration of this.
First create a table with sample data:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_Employee ( EmpID INT ,EmpName CHARACTER VARYING ); INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Roy') ,(3,'Benny'),(4,'Nivu'); |
Now we are going to test this option with the help of two different sessions.
Open a new session and execute below queries in Session 1:
We have inserted four sample records and now will update one of record in OPEN TRANSACTION with out committing anything.
|
1 2 3 4 |
BEGIN TRANSACTION UPDATE tbl_Employee SET EmpName = 'Martin' WHERE EmpID = 3; |
Open another session and execute below queries in Session 2:
First SELECT with out any option:
If you do not know, please read about the MVCC.
PostgreSQL has a default MVCC architecture, in which you can select last committed row version.
In the above query we have updated one record for EmpID=3, but when I execute simple SELECT statement then it returned the old value of EmpID=3.This data is based on last committed row version.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT *FROM tbl_Employee; /* Result: EmpID EmpName -------------------- 1 Anvesh 2 Roy 4 Nivu 3 Benny */ |
Now SELECT with NOWAIT option:
When you are trying to SELECT running transaction, It will raise the error like : can not obtain lock on row. You can not SELECT EmpID=3, but you can SELECT EmpID IN (1,2,4).
Please find out below query and the result.
|
1 2 3 4 5 6 7 8 9 |
SELECT *FROM tbl_Employee WHERE EmpID=3 FOR UPDATE NOWAIT; SELECT *FROM tbl_Employee FOR UPDATE NOWAIT; /* Result: ********** Error ********** ERROR: could not obtain lock on row in relation "tbl_employee" SQL state: 55P03 */ |
Now finally, we test SKIP LOCKED option:
A SKIP LOCKED option simply sikped the records or rows which are the part of ROW Level Locking.
You can find out in the below result, EmpID=3 is not in result because it is not committed yet and running in different session.
|
1 2 3 4 5 6 7 8 9 |
/* Result: SELECT *FROM tbl_Employee FOR UPDATE SKIP LOCKED; EmpID EmpName -------------------- 1 Anvesh 2 Roy 4 Nivu */ |
