SQL locking datasets
Concepts, when, how, challenge
A comparison between
Select For Update
VS
Select For Update SKIP LOCKED
How to implement a database job queue using SKIP LOCKED
Many developers still used #select for update# to ensuring locking on requested rows such as queuing systems.
Even if DBA recommends new features available feom 11g that is :
SELECT FOR UPDATE SKIP LOCKED.
Therefore, there is a fear in them that this method may not work properly😉 or that they may be reluctant to accept new features.
On the other hand, the use of the previous expression(for update) and usually the delay in commits will caused some problems on the database (performance penaly cause of locking sequentially) and this statements always make challenges for DBAs.
Introduction
Totally there are two type of locking:
Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.
This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.
Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.
For implementing of pessimistic locking we have 2 options in oracle:
Select for update
Select for update skip locked
In this article, we are going to see how we can implement a database job queue using SKIP LOCKED.
I decided to write this article to ensure developers to easy use this feature without fearing.
Since SKIP LOCKED is a lesser-known SQL feature, it’s a good opportunity to show you how to use it and why you should employ it, especially when implementing a job queue task.
Skip locked guarantees isolation:
SELECT FOR UPDATE SKIP LOCKED is undocumented in Oracle9i and Oracle10g.
It is used behind the scenes by Advanced Queuing, a reliable messaging service built into the ORACLE DBMS.
Oracle11g Release 1 is the first DBMS release that includes documentation on SELECT FOR UPDATE SKIP LOCKED.
The SKIP LOCKED clause improves the scalability of applications that attempt to concurrently update the same set of rows in a table.
It eliminates wait time for TX locks that leading challenge for Database and DBA.
Consistency and isolation are preserved.
The DBMS server assigns a fair share of the rows to each database client that is interested in an overlapping result set.
Let me explain moreover and deep inside:
Perhaps a better syntax would have been:
"skip locked by another session"
Skip locked was introduced (for Advanced Queuing:AQ) to allow concurrent access to rows without each session getting 'tangled' with the other.
Note:
You can't use the for update clause in subquery, as the docs state:
You can specify this clause only in a top-level SELECT statement, not in subqueries.
If you want to update only the unlocked rows, you'll have to split this into steps:
- Open a cursor
- Fetch the unlocked rows
- Run the update
e.g. something along the lines of:
declare
cursor cur is
select * from ...
for update skip locked;
begin
open cur;
loop
fetch cur bulk collect into ...;
exit when ...
forall rws in ...
update t ...
end loop;
close cur;
end;
/
Another example:
Assume you want to get the first row from a table that is not currently locked and matches some key - unique or otherwise (unique would not make sense, there would be only one - and it would be trivial to determine if it is locked or not without the 'skip locked', you would just use nowait).
so, the unique key case isn't remotely interesting, we don't need - never needed - skip locked, just a nowait.
You just query and fetch, eg:
SQL> select empno
from scott.emp
where job = 'CLERK'
/
EMPNO
----------
7369
7876
7900
7934
Sql> declare
l_rec scott.emp%rowtype;
cursor c is select * from scott.emp where job = 'CLERK' for update skip locked;
begin
open c;
fetch c into l_rec;
close c;
dbms_output.put_line( 'Result: empno = ' || l_rec.empno );
end;
/
Result : empno = 7369
--Return first empno.
We don't Commit still, so empno 7369 still locked, attention that we select all of 4 empno that have clerck job but only one row was locked because we fetxh only ine rows.
PL/SQL procedure successfully completed.
Sql> declare
pragma autonomous_transaction;
l_rec scott.emp%rowtype;
cursor c is select * from scott.emp where job = 'CLERK' for update skip locked;
begin
open c;
fetch c into l_rec;
close c;
dbms_output.put_line( 'Result: empno = ' || l_rec.empno );
commit;
end;
/
Result: empno = 7876
--Return second empno. And skiped first because that locked in previous block.
--We do Commit.
PL/SQL procedure successfully completed.
In above example we did get and locked a unique row per independent sessions or sequentially doing both psql blocks and see that we can doing required operations on different rows as same transaction or in 2 isolated sessions without geting row lock contention.
We want to do explicit control of our fetching, because:
open cursor for select ... for update;
will lock all rows the moment you open the cursor.
However,
open cursor for select ... for update skip locked;
does not lock *any* rows. They are locked as you *fetch*
The game is changed on Oracle 23c:
In there previous posts,I demonstrate new useful feature that can help developers to become feel free anout locking orders.
Lock-Free Reservations available in 23c, enable concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-Free Reservations are held on the rows instead of locking them. It verifies if the updates can succeed and defers the updates until the transaction commit time. Lock-Free Reservations improves the user experience and concurrency in transactions.
Using skip locked in code action:
Domain Model
Let’s assume we have the following Post entity which has a status Enum property looking as follows:
Post table: PostStatus values:
ID Number Pending
Title Number Approved
Body Varchar2 Spam
Status Number(1) or boolean
The PostStatus Enum encapsulates the moderation status of a given Post entity.
Therefore, when the Post is first created, the status is PENDING.
The site moderators are going to review the pending Post entries and change the state to either APPROVED or SPAM.
The PostStatus class looks as follows:
public enum PostStatus {
PENDING,
APPROVED,
SPAM
}
And the Post entity is also trivial to map as a JPA entity:
@Entity(name = "Post")
@Table(name = "post")
public class Post {
@Id
private Long id;
private String title;
private String body;
@Enumerated
private PostStatus status;
//Getters and setters omitted for brevity
}
Job queue
So, the associated post table acts as a job queue since the rows need to be moderated prior to being displayed to the user. If we have multiple concurrent users trying to moderate the Post entities, we need a way to coordinate their efforts to avoid having two moderators review the same Post record.
Let’s consider that we have the following Post entries to moderate:
for (long i = 0; i < 10; i++) {
Post post = new Post();
post.setId(i);
post.setTitle("High-Performance Java Persistence");
post.setBody(String.format("Chapter %d summary", i));
post.setStatus(PostStatus.PENDING);
entityManager.persist(post);
}
The first naive implementation would be to retrieve the first N Post rows while also locking them:
public List<Post> getAndLockPosts(
EntityManager entityManager,
PostStatus status,
int postCount) {
return entityManager.createQuery(
"select p " +
"from Post p " +
"where p.status = :status " +
"order by p.id", Post.class)
.setParameter("status", status)
.setMaxResults(postCount)
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.setHint(
"javax.persistence.lock.timeout",
LockOptions.NO_WAIT
)
.getResultList();
}
Notice that we are using the PESSIMISTIC_WRITE JPA LockModeType to instruct Hibernate to apply an exclusive lock on the underlying selected Post records.
The javax.persistence.lock.timeout JPA query hint instructs Hibernate to issue a NOWAIT option when applying the exclusive lock. Without using NO WAIT, the lock acquisition will block until it either acquires the row-level lock or the lock waiting period times out.
Now, if we call the getAndLockPost method from two concurrent Java threads:
final int postCount = 2;
doInJPA(entityManager -> {
assertEquals(
postCount,
getAndLockPosts(
entityManager,
PostStatus.PENDING,
postCount
).size()
);
try {
executeSync(() -> {
doInJPA(_entityManager -> {
assertEquals(
postCount,
getAndLockPosts(
_entityManager,
PostStatus.PENDING,
postCount
).size()
);
});
});
} catch (Exception e) {
assertEquals(
1,
Arrays.stream(ExceptionUtils.getThrowables(e))
.map(Throwable::getClass)
.filter(clazz -> clazz.equals(PessimisticLockException.class))
.count()
);
}
});
We can see that a PessimisticLockException is indeed thrown:
Session/user1:
SELECT
p.id AS id1_0_,
p.body AS body2_0_,
p.status AS status3_0_,
p.title AS title4_0_
FROM
post p
WHERE
p.status=0
ORDER BY
p.id
LIMIT 2
FOR UPDATE OF p NOWAIT
Session/user2:
SELECT
p.id AS id1_0_,
p.body AS body2_0_,
p.status AS status3_0_,
p.title AS title4_0_
FROM
post p
WHERE
p.status=0
ORDER BY
p.id
LIMIT 2
FOR UPDATE OF p NOWAIT
-- SQL Error: 0, SQLState: 55P03
-- ERROR: could not obtain lock on row in relation "post"
The reason the PessimisticLockException is thrown is that both concurrent transactions try to lock the same records since the second transaction has no way of knowing which records are already locked.
Using SKIP LOCKED
To fix this problem, we need to use the LockOptions.SKIP_LOCKED Hibernate query hint:
public List<Post> getAndLockPostsWithSkipLocked(
EntityManager entityManager,
PostStatus status,
int postCount) {
return entityManager
.createQuery(
"select p " +
"from Post p " +
"where p.status = :status " +
"order by p.id", Post.class)
.setParameter("status", status)
.setMaxResults(postCount)
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.setHint(
"javax.persistence.lock.timeout",
LockOptions.SKIP_LOCKED
)
.getResultList();
}
Now, when fetching the Post entries using two concurrent Java threads:
final int postCount = 2;
doInJPA(entityManager -> {
List<Post> pendingPosts = getAndLockPostsWithSkipLocked(
entityManager,
PostStatus.PENDING,
postCount
);
List<Long> ids = pendingPosts
.stream()
.map(Post::getId)
.collect(toList());
assertTrue(
ids.size() == 2 &&
ids.contains(0L) &&
ids.contains(1L)
);
executeSync(() -> {
doInJPA(_entityManager -> {
List<Post> _pendingPosts = getAndLockPostsWithSkipLocked(
_entityManager,
PostStatus.PENDING,
postCount
);
List<Long> _ids = _pendingPosts
.stream()
.map(Post::getId)
.collect(toList());
assertTrue(
_ids.size() == 2 &&
_ids.contains(2L) &&
_ids.contains(3L)
);
});
});
});
Everything will work just fine since the second transaction will skip the rows that were locked previously by the first transaction:
[User1]session1
SELECT
p.id AS id1_0_,
p.body AS body2_0_,
p.status AS status3_0_,
p.title AS title4_0_
FROM
post p
WHERE
p.status = 0
ORDER BY
p.id
LIMIT 2
FOR UPDATE OF p SKIP LOCKED
[user2]: session2
SELECT
p.id AS id1_0_,
p.body AS body2_0_,
p.status AS status3_0_,
p.title AS title4_0_
FROM
post p
WHERE
p.status = 0
ORDER BY
p.id
LIMIT 2
FOR UPDATE OF p SKIP LOCKED
Notice the SKIP LOCKED option appended by Hibernate to the FOR UPDATE clause. The SKIP LOCKED option will allow us to lock the rows that have not been locked previously. In our example, you can see that Alice has selected and locked the Post entities with the id values of 0and 1 while Bob selects and locks the Post records with the id values of 3 and 4.
Without this option, implementing a job queue using a relational database would be a very complex task.
The SKIP LOCKED option is nowadays supported by most relational database systems. The following list indicates the first database version that introduced support for SKIP LOCKED.
- Oracle 10g
- PostgreSQL 9.5
- SQL Server 2005
- MySQL 8.0
Conclusion
Both pessimistic and optimistic locking are useful techniques. Pessimistic locking is suitable when the cost of retrying a transaction is very high or when contention is so large that many transactions would end up rolling back if optimistic locking were used.
On the other hand, optimistic locking works even across multiple database transactions since it doesn’t rely on locking physical records.
📍Optimistic locking means exclusive lock is not used when reading a row so lost update or write skew is not prevented.
So, use optimistic locking:
- If lost update or write skew doesn't occur.
- Or, if there are no problems even if lost update or write skew occurs.
📍Pessimistic locking means exclusive lock is used when reading a row so lost update or write skew is prevented.
So, use pessimistic locking:
- If lost update or write skew occurs.
- Or if there are some problems if lost update or write skew occurs.
📍SKIP LOCKED is a very handy option when implementing concurrency control algorithms using a relational database. Now that SKIP LOCKED is widely supported, you should definitely use it if you need to implement a queue job inside the relational database system you are using.
📍Also Lock-Free Reservations available in 23c, enable concurrent transactions to proceed without being blocked on updates of heavily updated rows.
Regards,
Alireza Kamrani
Senior RDBMS Consultant
