Created
July 7, 2016 01:51
-
-
Save axhiao/c54c10ec125e077b4213cdbca92e20c1 to your computer and use it in GitHub Desktop.
my question about mysql get_lock
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| here is my application scenario: assuming there is a table in mysql, named orders including 4 fields(id, orderid, money, type), everytime I pick the last item of each orderid to manipulate. For examle, i choose one order whose orderid=777, i.e. this item (3, 777, 34.00, 1), to consume $2.00. Then this item firstly is changed into (3, 777, 34.00, 0), and then I insert one row into the table (4, 777, 32.00, 1). | |
| But with the concurrency, there are probably two sessions to select the item(3, 777, 34.00, 1) at the same time, then consume $2.00 based on the same item. Now I totally have items (3, 777, 34.00, 0), (4, 777, 32.00, 1), (5, 777, 32.00, 1). Normally, everytime it should consume money based on the very last item of the order, i.e., (3, 777, 34.00, 0), (4, 777, 32.00, 0), (5, 777, 30.00, 1) | |
| last = getInfoByOrderId(orderid, type); | |
| //... | |
| // if last does not satisfy some conditon, then return or throw exceptions directly | |
| //... | |
| TransactionTemplate.execute(new TransactionCallback() { | |
| String sql = "select * from " + last.getTableName() + " where id = " + last.getId() + " for update"; | |
| lastinfo = queryByIdForUpdate(sql); | |
| update(lastinfo); | |
| insertOneItem(consumeMoney); | |
| }); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
START TRANSACTION
last = SELECT id FROM tbl WHERE orderid=X AND type=1 FOR UPDATE
amount = 2
UPDATE tbl SET money=money-2, type=0 WHERE id=last
INSERT INTO tbl VALUES ( .....)
COMMIT