Concurrency programming is a very broad topic and it can be discussed in a book by itself. The most common form is threading concurrency, but it also manifests itself in several other scenarios like networking I/O, database access and event-based UI programming.
Concurrency is hard to deal with. Programmers are comfortable with single-threaded programming where lines of code are executed one by one and resources are accessed serially. But for any non trivial multi-threaded programs, it is much harder to understand, maintain, optimize and debug than the single-threaded version.
Even with those downsides, the huge performance gain with concurrent programming is the main factor that attracts programmers to devote time and effort to mastering it. While many articles try to make a clear distinction between concurrency and parallelism, this post will ignore the insignificant differences and mainly focus on the mechanism to protect shared resource to avoid race condition.
As we have seen in the previous post, InnoDB provide transaction support to group a bunch of operations to be executed in database in an all-or-nothing style. But we also need to understand how transactions are processed when there are multiple transactions at the same time.
The transaction could be arranged in a serial way where each transaction is totally isolated from each other in timeline, each transaction holding an exclusive lock during its life time. But the throughput(usually in terms of query per second) will be extremely low, and the hardware capability for multiple CPU/core can not be leveraged. To allow higher concurrency, InnoDB offers a row-level lock so that transactions operated on different rows will not block each other. Also, InnoDB provides both shared-lock and exclusive lock to allow multiple readers to read from the same row. Just like the mutex/semaphores in the multithreaded programming, those locks are essential primitives to protect shared resource from race condition. But in MySQL those locks are not accessible to programmers directly. Instead it is implicitly determined by data manipulation type, index design, query conditions and isolation levels.
Let's start with a case without proper locking.
r = models.Retailer.objects.get(mobile='...') r.balance += 100 r.save()
If the above code are executed concurrently in two separate sessions, the retailer's balance could be added 100 or 200 and the exact result will depend on the order of execution of query. With MySQL default isolation level(REPEATABLE READ), it does not even help to wrap this snippet inside a transaction because a plain
SELECT in a transaction does not lock the row either. We have to use one of the following syntax to lock rows.
|SELECT ... LOCK IN SHARE MODE||Shared Lock|
|SELECT ... FOR UPDATE||Exclusive Lock|
|UPDATE ...||Exclusive Lock|
In Django, the above issue can be fixed either by using the 'SELECT ... FOR UPDATE':
with transaction.atomic(): retailers = models.Retailer.objects.select_for_update() r = retailers.get(mobile='...') r.balance += 100 r.save()
or by 'UPDATE ...'
with transaction.atomic(): retailers = models.Retailer.objects.filter(mobile='...') retailers.update(balance=F('balance')+100)
The second approach is preferred since its lock duration is shorter.
Please think why the following approach is not concurrency safe:
with transaction.atomic(): retailers = models.Retailer.objects.filter(mobile='...') retailers.update(balance=retailers.balance+100)
Once an exclusive row-lock is acquired in a transaction, we are safe to manipulate on this row until the lock is released at the end of the transaction. For example, it's safe to get the ending balance after the deposit operation by issuing a plain SELECT:
with transaction.atomic(): retailers = models.Retailer.objects.filter(mobile='...') retailers.update(balance=F('balance')+100) ending_balance = retailers.balance # up to date
Locks are not free. If not carefully designed and implemented, the lock may become a performance killer. Here is some general guidelines to reduce lock contention.
Minimize the lock range. The row-locks are acquired on scanned rows instead of selected rows, so it is very important to build the right index and use the right condition to minimize the range of rows being scanned to reduce lock contention. Without proper index, a table scanning SELECT will lock the whole table
Minimize the lock duration. Operation that takes indefinite time to complete (like network I/O) should not be included in a transaction or at least it should be put before the lock is acquired.