5 ms·

One tool to add to your toolbox as a developer is named locks. Say you have a table for orders and a table for order items and a table for payments. Collectivel

One tool to add to your toolbox as a developer is named locks. Say you have a table for orders and a table for order items and a table for payments. Collectively they represent an order, but that is a higher level concept that isn't something an RDBMS understands. When the user on your site simultaneously sends two updates, such as "remove item from the cart" and "pay and finish", you will have a slew of simultaneous updates to the rows across the three tables. Unless you use the highest isolation level, you will get inconsistencies. If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order. You could do an initial read from the orders table with FOR UPDATE which will provide you an implicit lock on that order, but you still have issues with new rows being inserted into your order items table that will not be range locked. So use names/advisory locks: create a named lock called "order-1234" where 1234 is tbe ID of the order. Acquire it before doing any manipulations to the order across all its tables. Then release it. Semantics of names locks differ across RDBMS's. Unsurprisingly Postgres has saner and more flexible semantics than MySQL, but both are perfectly suitable for the purpose. This technique can save you a ton of headaches.

ellius 9y ago
This was an awesome comment. I've been trying to figure this out but haven't known what I was looking for. Thanks for the advice.
- IgorPartola 9y ago
  Just so you know, this isn't the only way to do this. You can use external systems for locks as well. Redis is a good candidate: http://loose-bits.com/2010/10/distributed-task-locking-in-celery.html http://loose-bits.com/2010/10/distributed-task-locking-in-ce...
  - skyde 9y ago
    I woulds strongly advise against using Redis for lock. Maybe something with a real consensus algorithm like ETCD or Zookeeper but otherwise it's better to keep the lock inside the RDBMS.
aidos 9y ago
Glad to hear you've had success with that technique as I've been thinking about trying it myself. My plan is to use the WITH FOR UPDATE technique because I figure if I always lock the one top level object in my tree it allows me to isolate that whole subsection of the tree (so long as I'm consistent with the locking). Basically it should give a way to serialize operations on subsections of the data (which makes a lot of sense in general usage where it's partitioned naturally).
- IgorPartola 9y ago
  I've done this before and it's a valid technique. It still has some edge cases that can bite you. First, you still have to make sure that you do all your reads and updates in a consistent order because otherwise you will deadlock. Second, and more important, you must use repeatable reads for this technique. Otherwise given my example of orders and order items, you can get really funny behavior. Say you have a function on class Order that recalculates totals with tax, shipping, etc. It's going to be based on the rows in the order items table. So you do your read of order row with FOR UPDATE, then read a list of items and do some calculations and update things. Then in the same transaction you read the order items again, except the user has now added another item to the cart. If adding items to the cart didn't lock the order with FOR UPDATE, that read would have gone through. Also, locks using FOR UPDATE don't have explicit lock mechanics. You can't check if that row is locked. You can't easily control the timeout. I consider it a bandaid technique. Bandaids are great for small boo boos, but they don't work if you already have a codebase that is complex and does things in an inconsistent order.
  - aidos 9y ago
    Thanks for the extra detail; it's always good to hear of any gotchas. In my system a lot of what happens is effectively a single user operates on a subset of data that nobody else touches but I'll definitely to a named lock prototype before I go down any particular path.
virtuabhi 9y ago
What is the granularity of the named locks? Seems like table level.
- IgorPartola 9y ago
  The granularity is literally the name of the lock. It's not attached to anything else in the DB. You get to define what the lock means in your code. If you use Django/Python + Postgres, here's an example of a nice abstraction on top of it: https://pypi.python.org/pypi/django-pglocks/1.0.1 https://pypi.python.org/pypi/django-pglocks/1.0.1 Think of these locks as mutexes in threaded code: they don't mean anything on their own, but they simply guard against the rest of your code. Contrived semi-pseudocode example: def add_item_to_cart(order, item): try: lock = acquire_named_lock('order-{}'.format(order.id), timeout=10) order.add_item(item) order.recalculate_total() order.save() lock.release() except LockTimeoutError: raise OrderUpdateError('Timed out trying to add item to order.') def checkout(order): try: lock = acquire_named_lock('order-{}'.format(order.id), timeout=10) order.pay() order.generate_and_email_invoice() order.print_shipping_label() order.save() lock.release() except LockTimeoutError: raise OrderUpdateError('Timed out trying to check out.') Note that the two functions will never step on each others' toes because the first thing they do is acquire a lock.
  - virtuabhi 9y ago
    Thanks, got it. It is nice to see database systems directly offering a locking system for use in applications.
  - SoMisanthrope 9y ago
    Does that give you a row-level lock that extends through the chain of referential integrity, for the Order?
    - IgorPartola 9y ago
      No. These advisory locks don't know anything about what you are locking. You name and you use the lock, not the RDBMS. That is you could give the lock the name `my-silly-lock-name` and that would totally work. As long as two bits of your code use the same lock name, they'll be executed in series. Hell, the code inside the code guarded by these locks doesn't even have to touch the DB. This is like a separate service your RDBMS provides that doesn't touch any tables, sequences, etc. Essentially these are mutexes identified by strings that are managed by your RDBMS. That's all.
daigoba66 9y ago
For those using MSSQL: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql https://docs.microsoft.com/en-us/sql/relational-databases/sy...
ivanhoe 9y ago
Reading this article made it much clearer to me why both locks and transactions are needed: https://www.brightbox.com/blog/2013/10/31/on-mysql-locks/ https://www.brightbox.com/blog/2013/10/31/on-mysql-locks/
- IgorPartola 9y ago
  You absolutely need both. They serve different purposes because transactions provide ACID and advisory locks can be used to provide higher level ordering of application code.
stickfigure 9y ago
If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order. It sounds like you misunderstand optimistic concurrency. If your transaction touches a piece of data, and that data changes (by another transaction) while your transaction is in progress, then your transaction will rollback on commit. With a tiny bit of library code, your transaction will retry until success. In the case of severe contention you may see repeated retries and timeouts, but you will never see deadlocks - at least one transaction will successfully commit each "round". I disagree - database locks should not be used by the average developer. Understand optimistic concurrency and use serializable isolation. If (and only if!) you have a known performance problem with highly contended data, consider a locking strategy. 99 out of 100 developers will never need it, and most of that 1% will botch locks on the first try anyways. If your database doesn't support optimistic concurrency, get a better database.
- IgorPartola 9y ago
  I partially agree with what you are saying. I understand optimistic concurrency, especially the constraints it places on your code: you must in all cases access the data in the same order, otherwise you do get deadlocks. You also must implement the retry mechanism. I am not familiar with all the web frameworks out there (this is where I often encounter these types of issues), but I don't know of one that automatically retries requests for you. That "tiny bit of library code" is something that as far as I know you have to create for every situation, and it will be specific to your situation. I have yet to see a production codebase that actually does this instead of simply returning an error to the user saying "try again". If your database doesn't fully support ACID transactions, yes, get a better DB. And no you don't need to use named locks all the time. As an example, I have a codebase where I use the standard transactions semantics everywhere using the Django ORM, except one very specific and critical bit of code where I want to be dead certain that a thing can't happen twice in a row. So basically out of, say, 100 or so places where I commit a transaction, only one actually uses named locks, but where it does, it solves the problem in a very elegant way. So I agree with you that you shouldn't litter your code with these. But you absolutely can use them to either (a) guard against complex and critical parts of your code or (b) use these to quickly augment a huge existing codebase that keeps running into deadlocks or worse yet lock timeouts.
lomnakkus 9y ago
> So use names/advisory locks: create a named lock called "order-1234" where 1234 is tbe ID of the order. If all your locking is single-transaction, then I think a better technique might actually to have a separate table where you have the individual lock IDs and use SELECT FOR UPDATE on the appropriate row there[1] -- only to obtain the lock. (Of course, most of the caveats you've spelled out in this thread still apply wrt. ordering, etc.) Reasoning: Most named lock/advisory lock implementations don't participate in the current transaction, so you can end with "stuck" locks where the lock was acquired, but never released because the transaction before the "release" action failed. It goes without saying that this is not a problem that can be solved client-side, so you end up having to have "lock cleanup" (and how can you be sure that cleaning up a lock is actually the right thing, etc.). [1] You might also have to UPSERT rows depending on how "dynamic" your lock IDs are, etc.

ellius 9y ago

This was an awesome comment. I've been trying to figure this out but haven't known what I was looking for. Thanks for the advice.

IgorPartola 9y ago
Just so you know, this isn't the only way to do this. You can use external systems for locks as well. Redis is a good candidate: http://loose-bits.com/2010/10/distributed-task-locking-in-celery.html http://loose-bits.com/2010/10/distributed-task-locking-in-ce...
- skyde 9y ago
  I woulds strongly advise against using Redis for lock. Maybe something with a real consensus algorithm like ETCD or Zookeeper but otherwise it's better to keep the lock inside the RDBMS.

aidos 9y ago

Glad to hear you've had success with that technique as I've been thinking about trying it myself. My plan is to use the WITH FOR UPDATE technique because I figure if I always lock the one top level object in my tree it allows me to isolate that whole subsection of the tree (so long as I'm consistent with the locking). Basically it should give a way to serialize operations on subsections of the data (which makes a lot of sense in general usage where it's partitioned naturally).

IgorPartola 9y ago
I've done this before and it's a valid technique. It still has some edge cases that can bite you. First, you still have to make sure that you do all your reads and updates in a consistent order because otherwise you will deadlock. Second, and more important, you must use repeatable reads for this technique. Otherwise given my example of orders and order items, you can get really funny behavior. Say you have a function on class Order that recalculates totals with tax, shipping, etc. It's going to be based on the rows in the order items table. So you do your read of order row with FOR UPDATE, then read a list of items and do some calculations and update things. Then in the same transaction you read the order items again, except the user has now added another item to the cart. If adding items to the cart didn't lock the order with FOR UPDATE, that read would have gone through. Also, locks using FOR UPDATE don't have explicit lock mechanics. You can't check if that row is locked. You can't easily control the timeout. I consider it a bandaid technique. Bandaids are great for small boo boos, but they don't work if you already have a codebase that is complex and does things in an inconsistent order.
- aidos 9y ago
  Thanks for the extra detail; it's always good to hear of any gotchas. In my system a lot of what happens is effectively a single user operates on a subset of data that nobody else touches but I'll definitely to a named lock prototype before I go down any particular path.

virtuabhi 9y ago

What is the granularity of the named locks? Seems like table level.

IgorPartola 9y ago
The granularity is literally the name of the lock. It's not attached to anything else in the DB. You get to define what the lock means in your code. If you use Django/Python + Postgres, here's an example of a nice abstraction on top of it: https://pypi.python.org/pypi/django-pglocks/1.0.1 https://pypi.python.org/pypi/django-pglocks/1.0.1 Think of these locks as mutexes in threaded code: they don't mean anything on their own, but they simply guard against the rest of your code. Contrived semi-pseudocode example: def add_item_to_cart(order, item): try: lock = acquire_named_lock('order-{}'.format(order.id), timeout=10) order.add_item(item) order.recalculate_total() order.save() lock.release() except LockTimeoutError: raise OrderUpdateError('Timed out trying to add item to order.') def checkout(order): try: lock = acquire_named_lock('order-{}'.format(order.id), timeout=10) order.pay() order.generate_and_email_invoice() order.print_shipping_label() order.save() lock.release() except LockTimeoutError: raise OrderUpdateError('Timed out trying to check out.') Note that the two functions will never step on each others' toes because the first thing they do is acquire a lock.
- virtuabhi 9y ago
  Thanks, got it. It is nice to see database systems directly offering a locking system for use in applications.
- SoMisanthrope 9y ago
  Does that give you a row-level lock that extends through the chain of referential integrity, for the Order?
  - IgorPartola 9y ago
    No. These advisory locks don't know anything about what you are locking. You name and you use the lock, not the RDBMS. That is you could give the lock the name `my-silly-lock-name` and that would totally work. As long as two bits of your code use the same lock name, they'll be executed in series. Hell, the code inside the code guarded by these locks doesn't even have to touch the DB. This is like a separate service your RDBMS provides that doesn't touch any tables, sequences, etc. Essentially these are mutexes identified by strings that are managed by your RDBMS. That's all.

daigoba66 9y ago

For those using MSSQL: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql https://docs.microsoft.com/en-us/sql/relational-databases/sy...

ivanhoe 9y ago

Reading this article made it much clearer to me why both locks and transactions are needed: https://www.brightbox.com/blog/2013/10/31/on-mysql-locks/ https://www.brightbox.com/blog/2013/10/31/on-mysql-locks/

IgorPartola 9y ago
You absolutely need both. They serve different purposes because transactions provide ACID and advisory locks can be used to provide higher level ordering of application code.

stickfigure 9y ago

If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order. It sounds like you misunderstand optimistic concurrency. If your transaction touches a piece of data, and that data changes (by another transaction) while your transaction is in progress, then your transaction will rollback on commit. With a tiny bit of library code, your transaction will retry until success. In the case of severe contention you may see repeated retries and timeouts, but you will never see deadlocks - at least one transaction will successfully commit each "round". I disagree - database locks should not be used by the average developer. Understand optimistic concurrency and use serializable isolation. If (and only if!) you have a known performance problem with highly contended data, consider a locking strategy. 99 out of 100 developers will never need it, and most of that 1% will botch locks on the first try anyways. If your database doesn't support optimistic concurrency, get a better database.

IgorPartola 9y ago
I partially agree with what you are saying. I understand optimistic concurrency, especially the constraints it places on your code: you must in all cases access the data in the same order, otherwise you do get deadlocks. You also must implement the retry mechanism. I am not familiar with all the web frameworks out there (this is where I often encounter these types of issues), but I don't know of one that automatically retries requests for you. That "tiny bit of library code" is something that as far as I know you have to create for every situation, and it will be specific to your situation. I have yet to see a production codebase that actually does this instead of simply returning an error to the user saying "try again". If your database doesn't fully support ACID transactions, yes, get a better DB. And no you don't need to use named locks all the time. As an example, I have a codebase where I use the standard transactions semantics everywhere using the Django ORM, except one very specific and critical bit of code where I want to be dead certain that a thing can't happen twice in a row. So basically out of, say, 100 or so places where I commit a transaction, only one actually uses named locks, but where it does, it solves the problem in a very elegant way. So I agree with you that you shouldn't litter your code with these. But you absolutely can use them to either (a) guard against complex and critical parts of your code or (b) use these to quickly augment a huge existing codebase that keeps running into deadlocks or worse yet lock timeouts.

lomnakkus 9y ago

> So use names/advisory locks: create a named lock called "order-1234" where 1234 is tbe ID of the order. If all your locking is single-transaction, then I think a better technique might actually to have a separate table where you have the individual lock IDs and use SELECT FOR UPDATE on the appropriate row there[1] -- only to obtain the lock. (Of course, most of the caveats you've spelled out in this thread still apply wrt. ordering, etc.) Reasoning: Most named lock/advisory lock implementations don't participate in the current transaction, so you can end with "stuck" locks where the lock was acquired, but never released because the transaction before the "release" action failed. It goes without saying that this is not a problem that can be solved client-side, so you end up having to have "lock cleanup" (and how can you be sure that cleaning up a lock is actually the right thing, etc.). [1] You might also have to UPSERT rows depending on how "dynamic" your lock IDs are, etc.