'PostgressSQL: how to select select X rows FOR UPDATE to prevent locking all rows for multiprocessing?
query:
cur.execute('''SELECT * FROM tracks WHERE sent_to_sqs IS NULL LIMIT 10 FOR UPDATE''')
locks all the rows in the table so multiple processing doesn't work b/c every process is waiting for the lock to be removed. How can I select just X rows so i'm not locking all the rows
code & output
def start_sending_msg_sqs(_):
"""
param: _ : discarded b/c just starting multiple processes at the same time
"""
def utility_create_single_sqs_entry(outer_id, products):
""" return: signle SQS entry for batch messsage
use results to append to a list to create a batch message
"""
id = outer_id
message_body = {
"type": "cache_track",
"data": {
"organization_id": organization_id,
"outer_id": outer_id,
"products": json.loads(products)
}
}
return {"Id": id, 'MessageBody': json.dumps(message_body)}
def worker_write_to_sqs(json_bodies_list):
""" collect 10 complete json_bodies to send to SQS
returns: -> response JSON from SQS
needs error handling
"""
boto3.setup_default_session(profile_name='company')
sqs = boto3.client('sqs')
response = sqs.send_message_batch(
QueueUrl=QUEUE_URL,
Entries=json_bodies_list
)
return response
def utility_check_sqs_response_and_update_db(response):
""" helper function: updates the tracks with "1 == sent_to_sqs to mark track as delivered"""
success_messages = response['Successful']
cur_update = conn.cursor()
for msg in success_messages: ## only mark successful batch messages as sent_to_sqs
outer_id = msg.get('Id')
cur_update.execute('''UPDATE tracks SET sent_to_sqs = %s WHERE outer_id= %s''', (1, outer_id)) ## ONLY WRITE STATEMENT
conn.commit() ## commit immediately
# print(outer_id, 'Done')
## MAIN ##
## create all connections to db
def main():
# cur_read = conn.cursor()
# cur2_update = conn.cursor()
cur.execute(
'''SELECT * FROM tracks WHERE sent_to_sqs IS NULL LIMIT 10 FOR UPDATE''') # read 10 messages and send only the alpha numeric ones.
batch_messages = []
for row in cur:
outer_id, ingestion_id, products, sent_to_sqs = row
# print(row)
if not re.match("^[\w\d_-]*$", outer_id):
cur.execute('''UPDATE tracks SET sent_to_sqs = ? WHERE outer_id= ?''',
[-1, outer_id]) ## ONLY WRITE STATEMENT
conn.commit()
# if sent_to_sqs != 1 and outer_id.isalnum():
if sent_to_sqs not in (1,-1) :
single_entry = utility_create_single_sqs_entry(outer_id=outer_id, products=products)
batch_messages.append(single_entry)
response = worker_write_to_sqs(json_bodies_list=batch_messages)
print(f"process_id: {os.getpid()}, response: {response}")
# pprint(response)
utility_check_sqs_response_and_update_db(response=response)
def check_remaining_rows():
# cur = conn.cursor()
cur.execute('''SELECT * FROM tracks WHERE sent_to_sqs IS NULL LIMIT 10 FOR UPDATE''')
num_rows = cur.fetchall()
return num_rows
conn = psycopg2.connect(
"host=localhost dbname=catalog_store_tracks_dev_postgress user=name")
cur = conn.cursor()
while check_remaining_rows() not in (None, 0, -1):
main()
# start_sending_msg_sqs()
if __name__ == "__main__":
processes = 20 # Specify number of processes here
p = Pool(processes)
p.map(start_sending_msg_sqs, range(processes))
OUTPUT
process_id: 51878, response: {'Successful': [{'Id': 'QMPKX1635939', 'MessageId': 'b90ac999-543d-48b6-bd01-c8f162144a6e', 'MD5OfMessageBody': '0c944008703d118911d40aa79269763c'}, {'Id': 'USMC17800051', 'MessageId': 'abd1b5b1-12c0-49ce-a91a-6ccdb686e6b6', 'MD5OfMessageBody': '5816082f7ba728b5817ed36f06521be7'}, {'Id': 'DEGA21301016', 'MessageId': 'b5c02988-677f-423c-8330-9764931bc8ca', 'MD5OfMessageBody': 'efaf9d6c7095fd68ab143e4ab0f7fe2e'}, {'Id': 'DEKE51201420', 'MessageId': 'd2d4c1d5-42db-4e07-8ab3-59238e91ed62', 'MD5OfMessageBody': '47af4f90560486b4dd92b4a50f48f2d0'}, {'Id': 'USQY50972535', 'MessageId': '2112edbf-d9e2-4c24-8625-b4998c8f9bd3', 'MD5OfMessageBody': '321fbbffa12bf6edfac3d7b81fd70abc'}, {'Id': 'US4GZ1604208', 'MessageId': '5728b0df-9964-462d-b64e-f39fcb09fce5', 'MD5OfMessageBody': 'c2c8d42f15efeec308ad3b0f3742940c'}, {'Id': 'US3440449203', 'MessageId': '76ddc4b9-f93c-4c52-92f7-df048d514b76', 'MD5OfMessageBody': '4a3455bfc7b96044625cf07a54ebe2fd'}, {'Id': 'QM2PV1751732', 'MessageId': 'b310a54b-6461-4ab6-93af-40f5be479b5e', 'MD5OfMessageBody': 'ac347973df0c16eb68f5bde0278632a1'}, {'Id': 'QMGPD1300006', 'MessageId': '8bd854bd-f678-4479-bbe7-513b26f17b6a', 'MD5OfMessageBody': '2cfb2e5e78da537e7ab6c11afb5d898c'}, {'Id': 'NLE800900442', 'MessageId': '93e248ea-01f8-4e58-8d3b-098a0f92b7d3', 'MD5OfMessageBody': '45298cffa4d21cec9043ba5739a29091'}], 'ResponseMetadata': {'RequestId': '5bdbb664-aa63-58ab-b9cc-ecc8c1da8f2a', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '5bdbb664-aa63-58ab-b9cc-ecc8c1da8f2a', 'date': 'Wed, 16 Mar 2022 22:49:29 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51869, response: {'Successful': [{'Id': 'USMC17800051', 'MessageId': 'd452d51f-f30b-48d4-a6d0-71d709d4cbb2', 'MD5OfMessageBody': '5816082f7ba728b5817ed36f06521be7'}, {'Id': 'DEGA21301016', 'MessageId': '1582e33d-8fd5-4529-8096-d226f79b127e', 'MD5OfMessageBody': 'efaf9d6c7095fd68ab143e4ab0f7fe2e'}, {'Id': 'DEKE51201420', 'MessageId': '78fe505a-16c8-4ab2-921c-03a76e69bda7', 'MD5OfMessageBody': '47af4f90560486b4dd92b4a50f48f2d0'}, {'Id': 'USQY50972535', 'MessageId': 'f904839e-afe2-4dc3-8222-5959b3cc4b24', 'MD5OfMessageBody': '321fbbffa12bf6edfac3d7b81fd70abc'}, {'Id': 'US4GZ1604208', 'MessageId': '83502e8e-ca4c-49ba-ac7c-bdec9c6d832d', 'MD5OfMessageBody': 'c2c8d42f15efeec308ad3b0f3742940c'}, {'Id': 'US3440449203', 'MessageId': '26c9f8d4-841e-45f0-9880-45b3d3bd03b5', 'MD5OfMessageBody': '4a3455bfc7b96044625cf07a54ebe2fd'}, {'Id': 'QM2PV1751732', 'MessageId': 'ceae4fb8-9b50-4d97-a7a5-ab1f5cccc509', 'MD5OfMessageBody': 'ac347973df0c16eb68f5bde0278632a1'}, {'Id': 'QMGPD1300006', 'MessageId': 'ca074936-48c1-4ead-97dd-3871a9702ed9', 'MD5OfMessageBody': '2cfb2e5e78da537e7ab6c11afb5d898c'}, {'Id': 'NLE800900442', 'MessageId': 'f8876c74-d4a1-40d9-b66a-07b0669ce11a', 'MD5OfMessageBody': '45298cffa4d21cec9043ba5739a29091'}, {'Id': 'USA2Z1210961', 'MessageId': 'c35b275a-7abb-43b4-9cce-6caf5dd45836', 'MD5OfMessageBody': 'd1e669f0c2651946f82ba9159ebe285f'}], 'ResponseMetadata': {'RequestId': '033b0c7d-c68b-55ea-9042-56d1ad31506e', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '033b0c7d-c68b-55ea-9042-56d1ad31506e', 'date': 'Wed, 16 Mar 2022 22:49:30 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51870, response: {'Successful': [{'Id': 'DEGA21301016', 'MessageId': 'c2bf256d-dd3e-4677-9632-ec20a1610178', 'MD5OfMessageBody': 'efaf9d6c7095fd68ab143e4ab0f7fe2e'}, {'Id': 'DEKE51201420', 'MessageId': 'b358b1da-1d77-4e5a-ade4-09103696471a', 'MD5OfMessageBody': '47af4f90560486b4dd92b4a50f48f2d0'}, {'Id': 'USQY50972535', 'MessageId': '5ed29552-3b32-4c0e-81a3-8352f217555f', 'MD5OfMessageBody': '321fbbffa12bf6edfac3d7b81fd70abc'}, {'Id': 'US4GZ1604208', 'MessageId': 'cac4ddb3-9c7f-4f73-aefa-fc8ff6a13a2b', 'MD5OfMessageBody': 'c2c8d42f15efeec308ad3b0f3742940c'}, {'Id': 'US3440449203', 'MessageId': 'e85d7099-c4fb-4864-8f98-1db1d59e9d8a', 'MD5OfMessageBody': '4a3455bfc7b96044625cf07a54ebe2fd'}, {'Id': 'QM2PV1751732', 'MessageId': 'd2d5bb63-69ce-41e4-9963-9ddbf6698712', 'MD5OfMessageBody': 'ac347973df0c16eb68f5bde0278632a1'}, {'Id': 'QMGPD1300006', 'MessageId': '0e2a8975-e7b1-4969-9319-f0dfca3770c8', 'MD5OfMessageBody': '2cfb2e5e78da537e7ab6c11afb5d898c'}, {'Id': 'NLE800900442', 'MessageId': '8f9d3d8a-8793-4870-b458-ba4fc6620e1a', 'MD5OfMessageBody': '45298cffa4d21cec9043ba5739a29091'}, {'Id': 'USA2Z1210961', 'MessageId': '5290746a-7ff2-43ee-94f7-93f3756c7d3b', 'MD5OfMessageBody': 'd1e669f0c2651946f82ba9159ebe285f'}, {'Id': 'GBAYC8501509', 'MessageId': 'f5b65a2d-a330-4f40-a036-914508ea4ca0', 'MD5OfMessageBody': 'b35770780456453ac1151c560f748947'}], 'ResponseMetadata': {'RequestId': 'a2cb3f3b-c468-58a9-b6a5-6597afd39b88', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'a2cb3f3b-c468-58a9-b6a5-6597afd39b88', 'date': 'Wed, 16 Mar 2022 22:49:31 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51873, response: {'Successful': [{'Id': 'DEKE51201420', 'MessageId': '27a821a4-39ae-40d4-898b-25907442ac94', 'MD5OfMessageBody': '47af4f90560486b4dd92b4a50f48f2d0'}, {'Id': 'USQY50972535', 'MessageId': 'ce2f0fbc-9089-4ae0-bc17-23b5bbd5c038', 'MD5OfMessageBody': '321fbbffa12bf6edfac3d7b81fd70abc'}, {'Id': 'US4GZ1604208', 'MessageId': '1305a73e-18ad-4a61-b01d-4a274ed2a295', 'MD5OfMessageBody': 'c2c8d42f15efeec308ad3b0f3742940c'}, {'Id': 'US3440449203', 'MessageId': '37de7751-5098-44cb-8cc6-3844319b4f43', 'MD5OfMessageBody': '4a3455bfc7b96044625cf07a54ebe2fd'}, {'Id': 'QM2PV1751732', 'MessageId': 'f85e4305-d9ed-46f8-a30b-635b0f519069', 'MD5OfMessageBody': 'ac347973df0c16eb68f5bde0278632a1'}, {'Id': 'QMGPD1300006', 'MessageId': '90667e08-56d8-4dac-b596-275e45320190', 'MD5OfMessageBody': '2cfb2e5e78da537e7ab6c11afb5d898c'}, {'Id': 'NLE800900442', 'MessageId': 'ac5914c4-3d42-43b6-be79-4a7863aff0fc', 'MD5OfMessageBody': '45298cffa4d21cec9043ba5739a29091'}, {'Id': 'USA2Z1210961', 'MessageId': '7d7cd931-1a1b-4830-8e47-3a70ea372e96', 'MD5OfMessageBody': 'd1e669f0c2651946f82ba9159ebe285f'}, {'Id': 'GBAYC8501509', 'MessageId': '40bf3411-c05d-4fc4-9d25-30e22806522f', 'MD5OfMessageBody': 'b35770780456453ac1151c560f748947'}, {'Id': 'FRZ119502600', 'MessageId': 'e60de665-d5f9-4031-afdc-bef6846121b8', 'MD5OfMessageBody': '5495ab4a5b10c15c636d7da3fa68f7ac'}], 'ResponseMetadata': {'RequestId': '1f56d7ac-7efc-5ef9-ba66-8d001540ab05', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '1f56d7ac-7efc-5ef9-ba66-8d001540ab05', 'date': 'Wed, 16 Mar 2022 22:49:32 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51872, response: {'Successful': [{'Id': 'US3440449203', 'MessageId': 'acc1b7ac-2613-4e25-92d6-423c17fb2480', 'MD5OfMessageBody': '4a3455bfc7b96044625cf07a54ebe2fd'}, {'Id': 'QM2PV1751732', 'MessageId': 'ec944351-dfa4-43de-9af5-0967fa75b860', 'MD5OfMessageBody': 'ac347973df0c16eb68f5bde0278632a1'}, {'Id': 'QMGPD1300006', 'MessageId': '681fc476-2908-44f2-88f8-70fc1a9c5dfb', 'MD5OfMessageBody': '2cfb2e5e78da537e7ab6c11afb5d898c'}, {'Id': 'NLE800900442', 'MessageId': '7b28e4ea-0a71-4f30-b280-06191d2cbabd', 'MD5OfMessageBody': '45298cffa4d21cec9043ba5739a29091'}, {'Id': 'USA2Z1210961', 'MessageId': '567b89fc-5aae-4bb8-85c1-acff8c2bd35a', 'MD5OfMessageBody': 'd1e669f0c2651946f82ba9159ebe285f'}, {'Id': 'GBAYC8501509', 'MessageId': '4da50c46-3fbd-4890-bbd8-dadc5c050045', 'MD5OfMessageBody': 'b35770780456453ac1151c560f748947'}, {'Id': 'FRZ119502600', 'MessageId': 'a906f9cb-897d-4445-8ddc-e5591d3e0ff0', 'MD5OfMessageBody': '5495ab4a5b10c15c636d7da3fa68f7ac'}, {'Id': 'INN121001666', 'MessageId': 'dbc6ac95-94d2-4b5a-b3cf-f278b31f86df', 'MD5OfMessageBody': '565b168d89ee6808836339fccf1dbe9b'}, {'Id': 'QM6N21768871', 'MessageId': 'db563919-0021-40b8-957e-20dc3a9e52c0', 'MD5OfMessageBody': '540d5c998db95ce2325b64c1ead68739'}, {'Id': 'US2S71165013', 'MessageId': 'f8e2b703-b5a2-4b0d-a805-b367c55bf21e', 'MD5OfMessageBody': '3510392cb2a64ee6be24dbce75e7d198'}], 'ResponseMetadata': {'RequestId': '69075d30-1516-5c11-a207-079c7eabdd71', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '69075d30-1516-5c11-a207-079c7eabdd71', 'date': 'Wed, 16 Mar 2022 22:49:33 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51874, response: {'Successful': [{'Id': 'QMGPD1300006', 'MessageId': '46a82818-618b-4aa2-9f1c-0ba0ce99781d', 'MD5OfMessageBody': '2cfb2e5e78da537e7ab6c11afb5d898c'}, {'Id': 'NLE800900442', 'MessageId': '44ba32ff-31f7-49af-a93f-391764ddf602', 'MD5OfMessageBody': '45298cffa4d21cec9043ba5739a29091'}, {'Id': 'USA2Z1210961', 'MessageId': 'ab8638bb-72b3-4f7d-b065-2b1e1252c07f', 'MD5OfMessageBody': 'd1e669f0c2651946f82ba9159ebe285f'}, {'Id': 'GBAYC8501509', 'MessageId': '621fe6dd-bff4-444f-9c0e-f2fa9ab057ad', 'MD5OfMessageBody': 'b35770780456453ac1151c560f748947'}, {'Id': 'FRZ119502600', 'MessageId': 'c9249f97-41c1-4ab3-8996-ea7bcf1b9112', 'MD5OfMessageBody': '5495ab4a5b10c15c636d7da3fa68f7ac'}, {'Id': 'INN121001666', 'MessageId': '05051b48-c979-488f-acb9-12544f821fe3', 'MD5OfMessageBody': '565b168d89ee6808836339fccf1dbe9b'}, {'Id': 'QM6N21768871', 'MessageId': 'c9b8a494-9a3b-4603-8953-1a6dedccba4d', 'MD5OfMessageBody': '540d5c998db95ce2325b64c1ead68739'}, {'Id': 'US2S71165013', 'MessageId': '1ac89de7-187e-49c6-ba5e-14c520e5118d', 'MD5OfMessageBody': '3510392cb2a64ee6be24dbce75e7d198'}, {'Id': 'ushm21701904', 'MessageId': '2fb93a0d-650d-454d-9685-0f5d91c645a8', 'MD5OfMessageBody': '47013fcdf17883fa5fd8d6a546409664'}, {'Id': 'USBR20424928', 'MessageId': '5e10a84c-0450-4354-ab9d-f770000d5e7a', 'MD5OfMessageBody': '212f4468687e32bc51a4f63ec1d87fdf'}], 'ResponseMetadata': {'RequestId': 'ab83613a-4deb-5bdd-8f13-3b962656e19d', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'ab83613a-4deb-5bdd-8f13-3b962656e19d', 'date': 'Wed, 16 Mar 2022 22:49:33 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51877, response: {'Successful': [{'Id': 'USA2Z1210961', 'MessageId': '82c786b3-0bad-4a58-a68c-46569217006b', 'MD5OfMessageBody': 'd1e669f0c2651946f82ba9159ebe285f'}, {'Id': 'GBAYC8501509', 'MessageId': '96d91a3d-d87f-47c3-93d7-327487626896', 'MD5OfMessageBody': 'b35770780456453ac1151c560f748947'}, {'Id': 'FRZ119502600', 'MessageId': 'f8cbb03b-cd60-49a7-9d2b-bc5985111cd7', 'MD5OfMessageBody': '5495ab4a5b10c15c636d7da3fa68f7ac'}, {'Id': 'INN121001666', 'MessageId': '7a8c9832-5d6d-4197-99eb-59b0a2f67eb6', 'MD5OfMessageBody': '565b168d89ee6808836339fccf1dbe9b'}, {'Id': 'QM6N21768871', 'MessageId': 'bd485094-c9b7-479a-84f2-f7a6f2c8102a', 'MD5OfMessageBody': '540d5c998db95ce2325b64c1ead68739'}, {'Id': 'US2S71165013', 'MessageId': '079f02f9-0098-48ba-8345-4b77e3a43994', 'MD5OfMessageBody': '3510392cb2a64ee6be24dbce75e7d198'}, {'Id': 'ushm21701904', 'MessageId': '42c0e35a-f555-4652-8ef1-6665336e61b1', 'MD5OfMessageBody': '47013fcdf17883fa5fd8d6a546409664'}, {'Id': 'USBR20424928', 'MessageId': '0ac5c674-e7f1-45e2-94e3-680ef6aa039a', 'MD5OfMessageBody': '212f4468687e32bc51a4f63ec1d87fdf'}, {'Id': 'TCACH1534459', 'MessageId': '7211c606-a649-4b37-b0b8-2c465c1b143e', 'MD5OfMessageBody': 'b51170367ab05ecffa3290fb6abacaf4'}, {'Id': 'US3M51538526', 'MessageId': 'be6e13a4-5392-4633-bd01-78a0977bf3bb', 'MD5OfMessageBody': 'd48669db552eb80b09f1224b8b1b8dc6'}], 'ResponseMetadata': {'RequestId': '01fa5f9b-1ebb-5753-a3da-053775051756', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '01fa5f9b-1ebb-5753-a3da-053775051756', 'date': 'Wed, 16 Mar 2022 22:49:34 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51875, response: {'Successful': [{'Id': 'FRZ119502600', 'MessageId': '2075d19d-7ff3-43bd-960d-29054266b11e', 'MD5OfMessageBody': '5495ab4a5b10c15c636d7da3fa68f7ac'}, {'Id': 'INN121001666', 'MessageId': 'b88e53c9-a50e-48a8-a2a1-8f4625c528a4', 'MD5OfMessageBody': '565b168d89ee6808836339fccf1dbe9b'}, {'Id': 'QM6N21768871', 'MessageId': '88bc3f2b-f2e6-4c9d-9348-b3a38784b823', 'MD5OfMessageBody': '540d5c998db95ce2325b64c1ead68739'}, {'Id': 'US2S71165013', 'MessageId': '75d9553f-4605-4e19-8688-d08eb9945051', 'MD5OfMessageBody': '3510392cb2a64ee6be24dbce75e7d198'}, {'Id': 'ushm21701904', 'MessageId': '84ff9054-c06a-4c40-825a-1cabb847a5f1', 'MD5OfMessageBody': '47013fcdf17883fa5fd8d6a546409664'}, {'Id': 'USBR20424928', 'MessageId': '2169cf10-fbf2-4d29-a39d-e0d273877c1b', 'MD5OfMessageBody': '212f4468687e32bc51a4f63ec1d87fdf'}, {'Id': 'TCACH1534459', 'MessageId': 'd600cf4a-f1f6-4cb4-ac60-667cdc1317d2', 'MD5OfMessageBody': 'b51170367ab05ecffa3290fb6abacaf4'}, {'Id': 'US3M51538526', 'MessageId': '0308629d-d9bd-4880-b609-182a3b2b224e', 'MD5OfMessageBody': 'd48669db552eb80b09f1224b8b1b8dc6'}, {'Id': 'USZZR0912232', 'MessageId': '8603537a-93b0-4640-8e52-3b19f66f6533', 'MD5OfMessageBody': '440c387bed50de736c35e8d1a70aeddc'}, {'Id': 'GBBVL0000200', 'MessageId': '1115b61b-e871-406e-80c5-2ef5d5992260', 'MD5OfMessageBody': 'dc74bdbefdb28b4e84582290ebf876c4'}], 'ResponseMetadata': {'RequestId': '699147d9-fdb9-5e0b-8f93-1d759606a8a2', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '699147d9-fdb9-5e0b-8f93-1d759606a8a2', 'date': 'Wed, 16 Mar 2022 22:49:35 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51871, response: {'Successful': [{'Id': 'QM6N21768871', 'MessageId': '01439290-aca0-4dc5-98bf-88e1c9342470', 'MD5OfMessageBody': '540d5c998db95ce2325b64c1ead68739'}, {'Id': 'US2S71165013', 'MessageId': '51243810-decd-415d-af39-0078c76691e2', 'MD5OfMessageBody': '3510392cb2a64ee6be24dbce75e7d198'}, {'Id': 'ushm21701904', 'MessageId': '04285abb-1be3-4a8f-9ccc-55d016b9fe66', 'MD5OfMessageBody': '47013fcdf17883fa5fd8d6a546409664'}, {'Id': 'USBR20424928', 'MessageId': '48514377-1c12-48b6-aa7d-ebd1694e618d', 'MD5OfMessageBody': '212f4468687e32bc51a4f63ec1d87fdf'}, {'Id': 'TCACH1534459', 'MessageId': 'd1df57c6-ea9b-4992-819e-599be058ffb4', 'MD5OfMessageBody': 'b51170367ab05ecffa3290fb6abacaf4'}, {'Id': 'US3M51538526', 'MessageId': '178b7484-4d8d-45ae-8853-48e43c454b3c', 'MD5OfMessageBody': 'd48669db552eb80b09f1224b8b1b8dc6'}, {'Id': 'USZZR0912232', 'MessageId': '549a31aa-8ed2-4ebe-bd69-ed7843ba1f95', 'MD5OfMessageBody': '440c387bed50de736c35e8d1a70aeddc'}, {'Id': 'GBBVL0000200', 'MessageId': 'f066b738-dc36-4de5-a81b-e6262c8c4bb7', 'MD5OfMessageBody': 'dc74bdbefdb28b4e84582290ebf876c4'}, {'Id': 'TCADB1732117', 'MessageId': '6f887c0b-3374-485c-a607-88268cf35c26', 'MD5OfMessageBody': '7f2f674ec4a50fb8a03426dfa82122d7'}, {'Id': 'TCABD1273167', 'MessageId': '93d5d1ee-95d3-4cb7-8170-d22c3a0c56be', 'MD5OfMessageBody': '7c8f435e3e1fcc5557f40fe3b8fd4417'}], 'ResponseMetadata': {'RequestId': '499ff1bd-1f60-53a9-8454-ab1174a0b4ea', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '499ff1bd-1f60-53a9-8454-ab1174a0b4ea', 'date': 'Wed, 16 Mar 2022 22:49:36 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51876, response: {'Successful': [{'Id': 'ushm21701904', 'MessageId': 'b043ab33-2845-460b-9721-241d7c55eff4', 'MD5OfMessageBody': '47013fcdf17883fa5fd8d6a546409664'}, {'Id': 'USBR20424928', 'MessageId': 'ce2c0604-1501-4d6c-b3b0-753645d33dca', 'MD5OfMessageBody': '212f4468687e32bc51a4f63ec1d87fdf'}, {'Id': 'TCACH1534459', 'MessageId': '099d7c2e-9b8d-4614-9d5d-828b520e818e', 'MD5OfMessageBody': 'b51170367ab05ecffa3290fb6abacaf4'}, {'Id': 'US3M51538526', 'MessageId': 'a78bc423-281b-4b0f-ab04-ad88b07f1018', 'MD5OfMessageBody': 'd48669db552eb80b09f1224b8b1b8dc6'}, {'Id': 'USZZR0912232', 'MessageId': '3399665b-8243-4ba9-994c-0809f281d1bd', 'MD5OfMessageBody': '440c387bed50de736c35e8d1a70aeddc'}, {'Id': 'GBBVL0000200', 'MessageId': '62923e1c-1edd-497f-adca-8969ac9e2186', 'MD5OfMessageBody': 'dc74bdbefdb28b4e84582290ebf876c4'}, {'Id': 'TCADB1732117', 'MessageId': '003e5312-7795-4c4f-a21d-17037b5922e2', 'MD5OfMessageBody': '7f2f674ec4a50fb8a03426dfa82122d7'}, {'Id': 'TCABD1273167', 'MessageId': '1fc419b6-8638-4336-ab53-53482f63394b', 'MD5OfMessageBody': '7c8f435e3e1fcc5557f40fe3b8fd4417'}, {'Id': 'UST8K1613327', 'MessageId': 'daa3fdd8-e68d-4bf7-9830-f22e34adff63', 'MD5OfMessageBody': 'c09c3d66c84c20b5ead0067eb5f88076'}, {'Id': 'TCABF1229742', 'MessageId': '7764564e-7764-4d50-8af1-2d09472f6d5b', 'MD5OfMessageBody': '0604877f3c498869a831841199db42fd'}], 'ResponseMetadata': {'RequestId': '64d9c694-e9e9-5eff-a23c-9c38822891b8', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '64d9c694-e9e9-5eff-a23c-9c38822891b8', 'date': 'Wed, 16 Mar 2022 22:49:37 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51873, response: {'Successful': [{'Id': 'US3M51538526', 'MessageId': '0264ec99-bee6-49f3-939c-787768cd2dfe', 'MD5OfMessageBody': 'd48669db552eb80b09f1224b8b1b8dc6'}, {'Id': 'USZZR0912232', 'MessageId': 'e092e602-40ac-4b78-a073-9804308152e7', 'MD5OfMessageBody': '440c387bed50de736c35e8d1a70aeddc'}, {'Id': 'GBBVL0000200', 'MessageId': 'af41125b-9efb-49e6-b5c7-3a3441d77829', 'MD5OfMessageBody': 'dc74bdbefdb28b4e84582290ebf876c4'}, {'Id': 'TCADB1732117', 'MessageId': 'b44eb2ae-9f87-4d31-8897-3d2866d152d0', 'MD5OfMessageBody': '7f2f674ec4a50fb8a03426dfa82122d7'}, {'Id': 'TCABD1273167', 'MessageId': '4835ddcc-e38d-4584-af3c-132575a65d83', 'MD5OfMessageBody': '7c8f435e3e1fcc5557f40fe3b8fd4417'}, {'Id': 'UST8K1613327', 'MessageId': '3480cf85-5d24-44f1-b41f-794ed5e58186', 'MD5OfMessageBody': 'c09c3d66c84c20b5ead0067eb5f88076'}, {'Id': 'TCABF1229742', 'MessageId': '7df81f5b-967c-470f-a32f-4211917b6abe', 'MD5OfMessageBody': '0604877f3c498869a831841199db42fd'}, {'Id': 'ushm20902623', 'MessageId': 'dab9da33-a670-4910-ba2e-38aede293b9e', 'MD5OfMessageBody': 'ee6fbd106322ace98656da84fde0db3a'}, {'Id': 'USSM10004779', 'MessageId': '5178b485-efa1-4ec2-8355-69878f74ad94', 'MD5OfMessageBody': '9630795b88113bdd813585012908272d'}, {'Id': 'GBCJY0914804', 'MessageId': '624b3216-6f83-4ab6-a608-0e3eb59bb2ea', 'MD5OfMessageBody': 'b73a9566d1dde17dd023d7eefc571bff'}], 'ResponseMetadata': {'RequestId': 'a14fa011-2e11-5353-9f4d-fabd45d0c8b0', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'a14fa011-2e11-5353-9f4d-fabd45d0c8b0', 'date': 'Wed, 16 Mar 2022 22:49:38 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51870, response: {'Successful': [{'Id': 'USZZR0912232', 'MessageId': 'efa5cbb7-14c3-4291-95fa-d5ad9a8207aa', 'MD5OfMessageBody': '440c387bed50de736c35e8d1a70aeddc'}, {'Id': 'GBBVL0000200', 'MessageId': 'afa0f3cf-f9df-4afe-960e-cde7e5267921', 'MD5OfMessageBody': 'dc74bdbefdb28b4e84582290ebf876c4'}, {'Id': 'TCADB1732117', 'MessageId': '2852f4d3-2cf0-40e5-a170-cd97c700ef78', 'MD5OfMessageBody': '7f2f674ec4a50fb8a03426dfa82122d7'}, {'Id': 'TCABD1273167', 'MessageId': 'd3ef2304-05be-40b4-88e9-285b67830c1a', 'MD5OfMessageBody': '7c8f435e3e1fcc5557f40fe3b8fd4417'}, {'Id': 'UST8K1613327', 'MessageId': '62f3030f-1e43-4562-b90d-e211df9c1910', 'MD5OfMessageBody': 'c09c3d66c84c20b5ead0067eb5f88076'}, {'Id': 'TCABF1229742', 'MessageId': '9e0e972f-2a84-4392-82a1-9f3a730c3182', 'MD5OfMessageBody': '0604877f3c498869a831841199db42fd'}, {'Id': 'ushm20902623', 'MessageId': 'e3a7c902-9bbf-40f2-9f92-c25cd3e8d2fb', 'MD5OfMessageBody': 'ee6fbd106322ace98656da84fde0db3a'}, {'Id': 'USSM10004779', 'MessageId': 'e095c9b2-f65a-4ee0-8311-886290a70e95', 'MD5OfMessageBody': '9630795b88113bdd813585012908272d'}, {'Id': 'GBCJY0914804', 'MessageId': '5f7457ea-0cf0-40ef-80ce-1e546de31185', 'MD5OfMessageBody': 'b73a9566d1dde17dd023d7eefc571bff'}, {'Id': 'UST8K1775651', 'MessageId': '611fddf3-cd7b-4a53-a33c-342cb71b28d4', 'MD5OfMessageBody': '1fb1c7a1fce71775e35d0ad5f71ab552'}], 'ResponseMetadata': {'RequestId': '9add32f0-a745-5165-8c4e-685ccc87fca5', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '9add32f0-a745-5165-8c4e-685ccc87fca5', 'date': 'Wed, 16 Mar 2022 22:49:38 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51869, response: {'Successful': [{'Id': 'TCADB1732117', 'MessageId': '298310af-3015-458d-a423-aad7bb8e6c3a', 'MD5OfMessageBody': '7f2f674ec4a50fb8a03426dfa82122d7'}, {'Id': 'TCABD1273167', 'MessageId': 'f91200ef-8f09-48b5-970d-13b2c7eac0fd', 'MD5OfMessageBody': '7c8f435e3e1fcc5557f40fe3b8fd4417'}, {'Id': 'UST8K1613327', 'MessageId': '64087581-3174-4a5c-83a4-af314e98f322', 'MD5OfMessageBody': 'c09c3d66c84c20b5ead0067eb5f88076'}, {'Id': 'TCABF1229742', 'MessageId': '28e4ce3a-6866-406c-98ac-bbfe5b733e2d', 'MD5OfMessageBody': '0604877f3c498869a831841199db42fd'}, {'Id': 'ushm20902623', 'MessageId': 'f1094615-b8a0-47ef-8348-a97524e0597d', 'MD5OfMessageBody': 'ee6fbd106322ace98656da84fde0db3a'}, {'Id': 'USSM10004779', 'MessageId': '0bb9a94e-2660-4d9f-b662-d470c22b689e', 'MD5OfMessageBody': '9630795b88113bdd813585012908272d'}, {'Id': 'GBCJY0914804', 'MessageId': 'f3d70b74-f3c8-4e03-9e19-28c1076a2066', 'MD5OfMessageBody': 'b73a9566d1dde17dd023d7eefc571bff'}, {'Id': 'UST8K1775651', 'MessageId': 'fa024756-f5d7-4f2b-a196-e774f99c9890', 'MD5OfMessageBody': '1fb1c7a1fce71775e35d0ad5f71ab552'}, {'Id': 'USPA30100125', 'MessageId': '52cf3984-4e9a-471a-8b86-bae4c2cff469', 'MD5OfMessageBody': 'ebfb01e8088dd2fa895ebaf6c0efd3b9'}, {'Id': 'US2CT1110096', 'MessageId': '5428b75a-835d-4a54-b265-2e1f5df57a72', 'MD5OfMessageBody': '153c73da7d10ff983d268ab0d7aae041'}], 'ResponseMetadata': {'RequestId': 'f984ae21-a77e-5b98-9610-f48dccbda7b9', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'f984ae21-a77e-5b98-9610-f48dccbda7b9', 'date': 'Wed, 16 Mar 2022 22:49:39 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
process_id: 51872, response: {'Successful': [{'Id': 'UST8K1613327', 'MessageId': '357a89bb-ee0f-4bee-899e-cefd17147b82', 'MD5OfMessageBody': 'c09c3d66c84c20b5ead0067eb5f88076'}, {'Id': 'TCABF1229742', 'MessageId': '50234620-6c2f-45f6-b72e-a5434accdc2a', 'MD5OfMessageBody': '0604877f3c498869a831841199db42fd'}, {'Id': 'ushm20902623', 'MessageId': '3a8f321f-987a-42df-b78a-542c2e0e150f', 'MD5OfMessageBody': 'ee6fbd106322ace98656da84fde0db3a'}, {'Id': 'USSM10004779', 'MessageId': '5793aa4f-6ef6-43fe-82e5-328444850f98', 'MD5OfMessageBody': '9630795b88113bdd813585012908272d'}, {'Id': 'GBCJY0914804', 'MessageId': '95fa8e66-c947-4416-8397-31f471724141', 'MD5OfMessageBody': 'b73a9566d1dde17dd023d7eefc571bff'}, {'Id': 'UST8K1775651', 'MessageId': '097e8ed4-c71f-4f33-b4b5-69a248ce7af7', 'MD5OfMessageBody': '1fb1c7a1fce71775e35d0ad5f71ab552'}, {'Id': 'USPA30100125', 'MessageId': '4cce1db6-0c77-4a6d-a42c-b307b76f3e19', 'MD5OfMessageBody': 'ebfb01e8088dd2fa895ebaf6c0efd3b9'}, {'Id': 'US2CT1110096', 'MessageId': '7e903086-266b-47a3-b2f4-0347aaf863c8', 'MD5OfMessageBody': '153c73da7d10ff983d268ab0d7aae041'}, {'Id': 'US25T9912429', 'MessageId': 'f64e590f-1d91-4eb3-8680-482bb4520283', 'MD5OfMessageBody': '40142d08acd6e08989684edc0dab2761'}, {'Id': 'GBARL1401361', 'MessageId': '5fc16952-c0a9-43b9-918a-eb7c575ec911', 'MD5OfMessageBody': '149647b1a7b39d33ce85dd6c2d022bfb'}], 'ResponseMetadata': {'RequestId': 'e9a21ae6-07d0-5972-ad3a-404a6c142b19', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'e9a21ae6-07d0-5972-ad3a-404a6c142b19', 'date': 'Wed, 16 Mar 2022 22:49:40 GMT', 'content-type': 'text/xml', 'content-length': '2350'}, 'RetryAttempts': 0}}
notes:
- "Id" are often repeated.
- I think I created one connection per process by creating it just once in line 88.
- every select statement uses SELECT ... FOR UPDATE which should lock the rows b/w multiple processes and there should not be duplicate Id between different pid...
- Row locking is working, just not perfectly. definitely an improvement over SQLite3 as that id's repeat every row since there is not row locking.
update:
I think this line is actually preventing multithreading since it's locking all the rows...
SELECT * FROM ... LIMIT 10 FOR UPDATE
how to "select X rows FOR UPDATE" so i don't have to lock all rows...
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|