locking

PostgreSQL locking, part 3: lightweight locks

LWLocks lightweight locks postgresPostgreSQL lightweight locks, or LWLocks, control memory access. PostgreSQL uses multi-process architecture and should allow only consistent reads and writes to shared memory structures. LWLocks have two levels of locking: shared and exclusive. It’s also possible to release all acquired LWLocks to simplify clean up. Other databases often call primitives similar to LWLocks “latches”. Because […]

PostgreSQL locking, part 2: heavyweight locks

Locking in PostgreSQLPostgreSQL 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 make […]

PostgreSQL locking, Part 1: Row Locks

PostgreSQL row level locksAn 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 data. […]

New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning

Continuing our series of live, online classes, Kimberly will be delivering her new IETLB: Immersion Event on Transactions, Locking, Blocking, Isolation, and Versioning in October! The class will be delivered live via WebEx on October 9-11 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end […]

The post New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning appeared first on Paul S. Randal.

New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning

Continuing our series of live, online classes, Kimberly will be delivering her new IETLB: Immersion Event on Transactions, Locking, Blocking, Isolation, and Versioning in October! The class will be delivered live via WebEx on October 9-11 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end […]

The post New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning appeared first on Paul S. Randal.

InnoDB locks and transaction isolation level

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 whole table.
> Some bad select (read) query can do full table scan on InnoDB, does it lock whole table please?
> My understanding was that SELECT (read) blocks another DML only in MyISAM.

To answer this question, we to need understand two different concepts: locking and transaction isolation level.
In the default transaction isolation mode for InnoDB, REPEATABLE READ and the lower TRANSACTION ISOLATION levels, SELECT  doesn’t block any DML unless it uses SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE. On slide 20 (31) of my presentation, I use the SELECT … FOR UPDATE statement (this is why a lock is seen).
However, if transaction isolation mode is SERIALIZABLE, then SELECT can block updates. You can see this in the example below:mysql1> set transaction isolation level serializable;
Query OK, 0 rows affected (0,00 sec)
mysql1> begin;
Query OK, 0 rows affected (0,00 sec)
mysql1> select * from employees join titles using(emp_no);
c936e6fc4c6cbaf77679ba5013339dff –
443308 rows in set (29,69 sec)
mysql2> begin;
Query OK, 0 rows affected (0,00 sec)
mysql2> update titles set title=’Engineer’ where emp_no=15504;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionRegarding the different level locks set by InnoDB, I recommend that you follow the links on slide 37 (67) of the presentation, especially “InnoDB Locking Explained With Stick Figures” by Bill Karwin. Innodb table locks are also explained very well in this blog post by Peter Zaitsev.

Low priority locking wait types

SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations require. At the start of any online index operation, it acquires a S (share) table lock. This lock will be blocked until […]

The post Low priority locking wait types appeared first on Paul S. Randal.

doing nothing on modern CPUs

Sometimes you don’t want to do anything. This is understandably human, and probably a sign you should either relax or get up and do something.
For processors, you sometimes do actually want to do absolutely nothing. Often this will be while waiting for a lock. You want to do nothing until the lock is free, but you want to be quick about it, you want to start work once that lock is free as soon as possible.
On CPU cores with more than one thread (e.g. hyperthreading on Intel, SMT on POWER) you likely want to let the other threads have all of the resources of the core if you’re sitting there waiting for something.
So, what do you do? On x86 there’s been the PAUSE instruction for a while and on POWER there’s been the SMT priority instructions.
The x86 PAUSE instruction delays execution of the next instruction for some amount of time while on POWER each executing thread in a core has a priority and this is how chip resources are handed out (you can set different priorities using special no-op instructions as well as setting the Relative Priority Register to map how these coarse grained priorities are interpreted by the chip).
So, when you’re writing spinlock code (or similar, such as the implementation of mutexes in InnoDB) you want to check if the lock is free, and if not, spin for a bit, but at a lower priority than the code running in the other thread that’s doing actual work. The idea being that when you do finally acquire the lock, you bump your priority back up and go do actual work.
Usually, you don’t continually check the lock, you do a bit of nothing in between checking. This is so that when the lock is contended, you don’t just jam every thread in the system up with trying to read a single bit of memory.
So you need a trick to do nothing that the complier isn’t going to optimize away.
Current (well, MySQL 5.7.5, but it’s current in MariaDB 10.0.17+ too, and other MySQL versions) code in InnoDB to “do nothing” looks something like this:
ulint ut_delay(ulint delay)
{
ulint i, j;
UT_LOW_PRIORITY_CPU();
j = 0;
for (i = 0; i < delay * 50; i++) {
j += i;
UT_RELAX_CPU();
}
if (ut_always_false) {
ut_always_false = (ibool) j;
}
UT_RESUME_PRIORITY_CPU();
return(j);
}
On x86, UT_RELAX_CPU() ends up being the PAUSE instruction.
On POWER, the UT_LOW_PRIORITY_CPU() and UT_RESUME_PRIORITY_CPU() tunes the SMT thread priority (and on x86 they’re defined as nothing).
If you want an idea of when this was all written, this comment may be a hint:
/*!< in: delay in microseconds on 100 MHz Pentium */
But, if you’re not on x86 you don’t have the PAUSE instruction, instead, you end up getting this code:
# elif defined(HAVE_ATOMIC_BUILTINS)
# define UT_RELAX_CPU() do { \
volatile lint volatile_var; \
os_compare_and_swap_lint(&volatile_var, 0, 1); \
} while (0)
Which you may think “yep, that does nothing and is not optimized away by the compiler”. Except you’d be wrong! What it actually does is generates a lot of memory traffic. You’re now sitting in a tight loop doing atomic operations, which have to be synchronized between cores (and sockets) since there’s no real way that the hardware is going to be able to work out that this is only a local variable that is never accessed from anywhere.
Additionally, the ut_always_false and j variable there is also attempts to trick the complier into not optimizing the loop away, and since ut_always_false is a global, you’re generating traffic to a single global variable too.
Instead, what’s needed is a compiler barrier. This simple bit of nothing tells the compiler “pretend memory has changed, so you can’t optimize around this point”.
__asm__ __volatile__ (“”:::”memory”)
So we can eliminate all sorts of useless non-work and instead do what we want: do nothing (a for loop for X iterations that isn’t optimized away by the compiler) and don’t have side effects.
In MySQL bug 74832 I detailed this with the appropriately produced POWER assembler. Unfortunately, this patch (submitted under the OCA) has sat since November 2014 (so, over 9 months) with no action. I’m a bit disappointed by that to be honest.
Anyway, the real moral of this story is: don’t implement your own locking primitives. You’re either going to get it wrong or you’ll be wrong in a few years when everything changes under you.
See also:

http://bugs.mysql.com/bug.php?id=74832
https://mariadb.atlassian.net/browse/MDEV-8684
https://software.intel.com/en-us/articles/benefitting-power-and-performance-sleep-loops
SMT Priorities in the book “Performance Optimization and Tuning Techniques for IBM Processors including POWER8”
https://www.ibm.com/developerworks/community/blogs/RedbooksVetter/entry/modifiying_simultaneous_multi_threading_on_power_processors?lang=en

Modelling a “simple” ITL problem

Today I have been looking at an application problem. Last night important client processing missed its SLA with the application reporting ORA-02049: timeout: distributed timeout waiting for lock. This post isn’t about the distributed lock timing out. But consider the evidence that further investigation into these distributed locks threw up. The parameter distributed_lock_timeout is set […]

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569