Tag - locks

PostgreSQL locking, part 2: heavyweight locks

Locking in PostgreSQL

PostgreSQL locking visibility for application developers and DBAs is in most cases related to heavyweight locks. Complex database locking operations require full instrumentation using views from the system catalog. It should be clear which object is locked by a specific database “backend” process. An alternative name for any lock is “bottleneck”. In order to […]

Read more

PostgreSQL locking, Part 1: Row Locks

PostgreSQL row level locks

An understanding of PostgreSQL locking is important to build scalable applications and avoid downtime. Modern computers and servers have many CPU cores and it’s possible to execute multiple queries in parallel. Databases containing many consistent structures with changes made by queries or background processes running in parallel could crash a database or even corrupt […]

Read more

Avoid Shared Locks from Subqueries When Possible

Shared Locks

In this blog post, we’ll look at how to avoid shared locks from subqueries.
I’m pretty sure most of you have seen an UPDATE statement matching rows returned from a SELECT query:

MySQL

update ibreg set k=1 where id in (select id from ibcmp where id > 90000);

1

update ibreg set k=1 where id in (select id from […]

Read more

MyRocks and LOCK IN SHARE MODE

LOCK IN SHARE MODE

In this blog post, we’ll look at MyRocks and the
LOCK IN SHARE MODE.
Those who attended the March 30th webinar “MyRocks Troubleshooting” might remember our discussion with Yoshinori on 
LOCK IN SHARE MODE.
I did more tests, and I can confirm that his words are true:
LOCK IN SHARE MODE works in MyRocks.
This quick example demonstrates this. The […]

Read more

InnoDB locks and transaction isolation level

Overlooked MySQL performance optimization

What is the difference between InnoDB locks and transaction isolation level? We’ll discuss it in this post.
Recently I received a question from a user about one of my earlier blog posts. Since it wasn’t sent as a comment, I will answer it here. The question:

> I am reading your article:
> https://www.percona.com/resources/technical-presentations/troubleshooting-locking-issues-percona-mysql-webinar
> Full table scan locks […]

Read more