3 ms·

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.

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.