3

We are writing an inventory system and I have some questions about sqlalchemy (postgresql) and transactions/sessions. This is a web app using TG2, not sure this matters but to much info is never a bad.

  1. How can make sure that when changing inventory qty's that i don't run into race conditions. If i understand it correctly if user on is going to decrement inventory on an item to say 0 and user two is also trying to decrement the inventory to 0 then if user 1s session hasn't been committed yet then user two starting inventory number is going to be the same as user one resulting in a race condition when both commit, one overwriting the other instead of having a compound effect.

  2. If i wanted to use postgresql sequence for things like order/invoice numbers how can I get/set next values from sqlalchemy without running into race conditions?

EDIT: I think i found the solution i need to use with_lockmode, using for update or for share. I am going to leave open for more answers or for others to correct me if I am mistaken.

TIA

Ominus
  • 5,501
  • 8
  • 40
  • 44

2 Answers2

3

If two transactions try to set the same value at the same time one of them will fail. The one that loses will need error handling. For your particular example you will want to query for the number of parts and update the number of parts in the same transaction.

There is no race condition on sequence numbers. Save a record that uses a sequence number the DB will automatically assign it.

Edit:

Note as Limscoder points out you need to set the isolation level to Repeatable Read.

stonemetal
  • 6,111
  • 23
  • 25
  • So if there are 5 items in stock. Session/trasaction 1 is going to decrement that by 2 (sold 2 of them), and Session/transaction 2 is going to check stock to see how many there are. If transaction 2 is run before transaction 1 is finished does transaction 2 know that there are 3 in stock or does it still see 5? – Ominus Jun 14 '12 at 13:46
  • What you want to do is query once to display how many items are in stock. Both users will see 5 items in stock, both request 3 items. When they make the request you start a transaction, request the number of items to make sure you can fill the request, then try to commit the transaction. If the two transactions happen at the same time, all queries for the number of items will return 5. The transaction that would have been the error in the race condition will fail. You handle that failure by restarting the transaction then discovering there aren't enough items and let the user know. – stonemetal Jun 14 '12 at 15:01
  • If that isn't acceptable(doing point of sells for concert tickets or some such.) When the user asks how many of part X are there you can lock part X for their use or set aside some number of parts while they are considering using that item. – stonemetal Jun 14 '12 at 15:09
  • 3
    "If two transactions try to set the same value at the same time one of them will fail" -- this behavior is dependent on the query's isolation level. With postgresql's default isolation level (read committed), the 2nd update will succeed with no error, and the row will contain an incorrect value. – limscoder Jun 14 '12 at 16:49
  • A query like `update tablename set columnname=columnname+? where tableid=?` would be safe in default (fastest) isolation level. But I don't think SQLAlchemy can be made to generate such query. – Tometzky Jun 14 '12 at 22:31
1

Setup the scenario you are talking about and see how your configuration handles it. Just open up two separate connections to test it.

Also read up on FOR UPDATE For Update and also on transaction isolation level Isolation Level

Kuberchaun
  • 29,160
  • 7
  • 51
  • 59