Yup, this is fixed by adding a @utils.synchronized decorator on the DB API method which using SQLAlchemy's with_lockmode. What is happening SHOULD look something like this (queries have been simplified to protect my sanity):
green1: START TRANSACTION;
green2: START TRANSACTION;
green1: SELECT * FROM fixed_ips WHERE instance_id is NULL LIMIT 1 FOR UPDATE;
green2: SELECT * FROM fixed_ips WHERE instance_id is NULL LIMIT 1 FOR UPDATE;
At this point "green2" is 100% locked until...
green1: UPDATE fixed_ips SET instance_id = '1' WHERE address = '192.168.0.0';
green1: COMMIT;
Then green2 is able to continue...
green2: UPDATE fixed_ips SET instance_id = '1' WHERE address = '192.168.0.1';
green2: COMMIT;
This very much should work, and is what we *should* being doing in this situation. However, since we're doing 'weird' things with SQLAlchemy + eventlet I don't exactly know which piece is failing (although it seems to be SQLAlchemy?).
I'm going to see if I can investigate a little more, because I don't want to blindly 'fix' this without knowing what is going on, but the linked branch seems to fix all of the issues I can reproduce on my end.
Yup, this is fixed by adding a @utils.synchronized decorator on the DB API method which using SQLAlchemy's with_lockmode. What is happening SHOULD look something like this (queries have been simplified to protect my sanity):
Eventlet Greenthread #1 (green1)
Eventlet Greenthread #2 (green2)
green1: START TRANSACTION;
green2: START TRANSACTION;
green1: SELECT * FROM fixed_ips WHERE instance_id is NULL LIMIT 1 FOR UPDATE;
green2: SELECT * FROM fixed_ips WHERE instance_id is NULL LIMIT 1 FOR UPDATE;
At this point "green2" is 100% locked until...
green1: UPDATE fixed_ips SET instance_id = '1' WHERE address = '192.168.0.0';
green1: COMMIT;
Then green2 is able to continue...
green2: UPDATE fixed_ips SET instance_id = '1' WHERE address = '192.168.0.1';
green2: COMMIT;
This very much should work, and is what we *should* being doing in this situation. However, since we're doing 'weird' things with SQLAlchemy + eventlet I don't exactly know which piece is failing (although it seems to be SQLAlchemy?).
I'm going to see if I can investigate a little more, because I don't want to blindly 'fix' this without knowing what is going on, but the linked branch seems to fix all of the issues I can reproduce on my end.