מניעת race condition ב-DB

yossiz

יש לי טבלה של charges. כל charge משוייך ל-org על ידי עמודת orgId. אני אמור לתת לכל charge מספר. המספר הזה הוא לפי ארגון.
היום כאשר אני מכניס שורה חדשה אני עושה ככה:

מושך מה-DB את מספר ה-charge הגבוה ביותר של הארגון.
מוסיף עליו 1.
שומר charge חדש עם מספר המעודכן.

עכשיו, ברור לכל שיש פה race condition אם מתווסף charge חדש לטבלה בין שלב 1 לשלב 3.

יש לי כרגע 5 רעיונות איך לפתור את הבעיה. אשמח לשמוע את דעתכם. יצויין שמדובר ב-postgres, ואני משתמש ב-Sequelize ORM לכן יש עדיפות לפתרון שאפשר לממש ב-Sequelize.

לעשות את התהליך בתוך טרנזקציה כאשר רמת הבידוד של הטרנזקציה הוא SERIALIZABLE. (רמת הבידוד ברירת המחדל לכאורה לא יפתור את הבעיה).
לעשות נעילה על הטבלה לפני ביצוע שלב 1.
לעשות עבור כל ארגון SEQUENCE ולקבל את מספר ה-charge הנוכחי מה-SEQUENCE.
לעשות את כל התהליך בפקודת SQL אחד.
לממש את הנעילה ברמת התוכנה בלי להזדקק לשירותי ה-DB.

אני לא אוהב את פתרון 1 כי זה מסבך את הקוד, כי זה לא מונע בעיית מקביליות אלא מכשיל את הטרנזקציה כאשר יש בעיה ואז אני צריך לטפל בזה בקוד ולנסות שוב.
לגבי פתרון 2, אני לא יודע עדיין על סוגי הנעילה שקיימים אבל ברור שאני רוצה נעילה הכי זולה. האם יש אפשרות לנעול רק הכנסת שורות חדשות עבור ארגון זו?
פתרון 3 דורש עשיית טריגר או הוספת קוד לייצר SEQUENCE עובר כל ארגון חדש.
פתרון 4, האם זה זהה ל-1 או ל-2? או שיש הבדלים?
פתרון 5: אני לא אוהב את הרעיון של נעילה ברמת התוכנה כאשר אני מקבל את הנעילה בחינם מה-DB (אם רק הייתי יודע איך אני אמור לממש את זה) אבל אולי זה הפתרון הכי פשוט ונקי?

אשמח לדעתכם.

yossiz

אגב, לא איכפת לי אם לא הבנתם את כל האופציות או שאתם לא מרגישים בקיאים מספיק בנושא, אשמח לשמוע גם איך אתם פתרתם בעיה דומה בעבר.

dovid

אני מתבייש שכל השאלות האלה מוכרות לי ובכל זאת אני לא יודע לענות עליהם.
אני יודע שבSQL SERVER נכון להיום אתה מוגן ב4, וזה מה שהייתי עושה ברוב המקרים. (אם לא הייתי משתמש ב1, במערכות שאני עובד איתם השגיאה לא תקרה לעולם כמעט).
כמובן שזה נדיר כי אני תמיד ישתמש בAUTOINCREMENT - למה זה לא מתאים לך??
https://stackoverflow.com/questions/44387891/how-to-insert-row-with-maxnumber-1-in-postgresql-transactionally-without-seq
https://stackoverflow.com/questions/25980960/sequence-vs-max-primary-key-value-1
https://stackoverflow.com/q/8956044/1271037

yossiz

@dovid אמר במניעת race condition ב-DB:

כמובן ששזה נדיר כי אני תמיד ישתמש בAUTOINCREMENT - למה זה לא מתאים לך??

כי אני צריך למספר אותם בנפרד פר ארגון - זה לא מספר גלובלי לכל הטבלה.

dovid

@yossiz
https://stackoverflow.com/a/30000470/1271037

yossiz

@dovid עוד לא עיינתי בכל המראי מקומות. אבל מישהו הביא לי עכשיו פתרון שאני נהנה מאוד ממנו.
זה שילוב של פתרון 2 (נעילה ברמת ה-DB) ופתרון 5 (נעילה ברמת הקוד):
כאשר מושכים את מספר ה-charge הגבוהה ביותר של הארגון מה-DB לנעול רק את השורה ההיא על ידי FOR UPDATE.

SELECT "chargeNumber" FROM charges WHERE orgId = <orgId> ORDER BY "chargeNumber" DESC LIMIT 1 FOR UPDATE;

מכיון שכל פעם שהקוד הזה רץ הוא מבקש לנעול את השורה ההיא, זה יפעל כמו mutex בקוד. לכן למרות שאין מניעה מצד ה-DB להכניס באמצע הטרנזקציה עוד שורה עם מספר זהה, יש מניעה מצד הקוד.

clickone

@yossiz לכאורה טריגר הוא הפיתרון הנכון כאן
אם כי אני כמעט מעולם לא השתמשתי בטריגר (למעט הכרה שלו)
יש מקרה אחד ויחיד שבו עד היום השתמשתי בטריגר, וגם זה היה מחוסר ברירה ולא מבחירה
(יכול להיות שאני טועה בזה, אבל אני לא אוהב להשתמש בזה)

מה מפריע לך בעידכון של שאילתת משנה?

update orgs set lastCharge=(
select max(chargeId) from charges where orgId=orges.orgeId
) where orgeId=?

yossiz

@clickone אמר במניעת race condition ב-DB:

מה מפריע לך בעידכון של שאילתת משנה?

אין לי שום בעיה עם זה. זה לכאורה פתרון מצויין, אבל אני רוצה גם ללמוד פתרונות לבעיה במקומות שבהם פתרון זה לא יהיה מעשי.
(חוץ מזה אני לא יודע עדיין איך לעשות שאילתת משנה ב-SEQUELIZE אבל זה אני יכול ללמוד בקלות)
גם אשמח להבין אם פתרון זה הוא זהה (ברמת מתחת למכסה) לאחד מהפתרונות האחרות (האם יש נעילה? טרנזקציה באיזה רמת בידוד?)

clickone

@yossiz
אני לא מכיר את postgres, אבל אני חושב שזה שווה ערך ל4 והוא עושה את 2 הפקודות ביחד, ולכאורה זה נועל את הDB

אני מנסה קודם להבין את הצורך
כמו שכתב @dovid , בד"כ משתמשים במיספור אוטומטי, למה זה לא המצב אצלך?
אא"כ (שזה מה ששאני מריח) שזה טבלה כמו של חשבוניות / קבלות ואתה צריך לדעת את המספר האחרון שיצא
ועדיין, לא הבנתי למה אתה שומר את הנתון הזה סטטי בorgs, במקום להביא אותו מחושב באותו הרגע שתצטרך?
ז"א, במקום למשוך את זה מהטבלה, וללכת ולחזור, אתה פשוט מוסיף את הנתונים בinsert ואז מיד את המס' אתה מכניס עם sub query

INSERT INTO charge
(
docNumber,
name,
amount
)
VALUES
(
(select max(chargeId) from charges where orgId=?) as docNumber,
"yossiz" as name,
100 as amount
)

שים לב שאני כותב את השאילתא ישר לכאן ללא בדיקה אז יכול להיות טעויות

yossiz

@clickone נראה לי שאנחנו לא משדרים באותו תדר...
מספר ה-charge שמור בטבלת ה-charges לא עם ה-org.
כדי לקבל את המספר הבא אני מושך את המספר הגבוהה ביותר של ארגון זה ומוסיף עליו עוד אחד.

ועדיין, לא הבנתי למה אתה שומר את הנתון הזה סטטי בorgs, במקום להביא אותו מחושב באותו הרגע שתצטרך?

ז"א, במקום למשוך את זה מהטבלה, וללכת ולחזור, אתה פשוט מוסיף את הנתונים בinsert ואז מיד את המס' אתה מכניס עם sub query

זה בדיוק מה שהתכוונתי בפתרון מס' 4. (וזה מה שחשבתי שאתה מתכוון בפוסט הראשון שלך, עכשיו קראתי שוב ואני רואה שלא הבנת את מבנה ה-DB נכון)
ואני עדיין רוצה להבין מה רמת הנעילה שזה עושה, האם זה נועל את כל הטבלה למשך הרצת הפקודה או שרק הוספת שורות עבור אותו ארגון.

clickone

@yossiz
ואין לך דרך לדעת מי קשור לאותו ORG?
אם כן, למה זה ככה? (מוזר לי שאתה מתבסס על הORG אבל אין עמודה שלו בטבלה השנייה)

כנראה אני אכן מפספס כאן משהו מאד מהותי
תוכל להסביר יותר מה הקשרים בין הטבלאות?

yossiz

@clickone עזוב טבלאות אחרות, בטעות הזכרתי שיש טבלה של org.
יש רק טבלה אחת של charges עם 3 עמודות (שקשורות לנושא שלנו): id, orgId, chargeNumber
ה-chargenumber אמור להיות מספר ה-charge לפי הארגון. זה לא יכול להיות autoincrement כי אפשר שתיים עם אותו מספר כאשר הם משוייכים לארגון אחר

clickone

@yossiz אמר במניעת race condition ב-DB:

ה-chargenumber אמור להיות מספר ה-charge של הארגון.

שזה תמיד האחרון שנכנסם + 1?
או שזה מספר קבוע?
כעת אני כן רואה שאתה כותב שיש בטבלה orgid

yossiz

@clickone אמר במניעת race condition ב-DB:

שזה תמיד האחרון שנכנסם + 1?

בדיוק, אבל האחרון של ארגון זה, לא של כל הטבלה

clickone

@yossiz
אז מצויין, זה מה שכתבתי בדוגמא הזו
(תחליף את docNumber ב chargeNumber)

INSERT INTO charge
(
docNumber,
name,
amount
)
VALUES
(
(select max(chargeId) from charges where orgId=?) as docNumber,
"yossiz" as name,
100 as amount
)

ואתה התכוונת אליו למעלה, לא?
לכאורה בשיטה הזו הוא ינעל את השינויים כי זה נעשה בפעם אחת

clickone

כאן
https://stackoverflow.com/questions/39498675/how-to-create-an-auto-increment-column-that-is-segmented-by-an-other-column
לכאורה הוא פתר את זה עם טריגר ולכאורה זה הנכון
(זה נראה MSSQL אבל לא התעמקתי)

clickone

סליחה שאני שם כאן כ"כ הרבה מראי מקומות
מאד תמוה בעצם שאין דבר כזה מובנה כי זה דבר די נפוץ מה שאתה מנסה לעשות
אבל ראיתי שזה קיים בmysql אבל רק עם המנוע MyISAM
https://dba.stackexchange.com/questions/19019/mysql-get-next-unique-value-without-auto-increment/19020#19020
הרעיון הוא שאתה מגדיר שדה שהערך שלו יגדל ב1 על סמך שדה אחר בטבלה

PRIMARY KEY (SiteID,SiteWorkorderNum)

והנה כל הדוגמא משם

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

yossiz

@clickone מעניין מאוד, זה בדיוק מה שחיפשתי (אבל זה לא קיים ב-postgres).

clickone

@yossiz
כעת מצאתי משהו דומה על mssql
שזה יצירת אינדקס מותאם אישית, שעושה בדיוק את הפעולה הזו
https://stackoverflow.com/questions/12152790/sql-server-autoincrement-varying-by-value-of-another-field
עכשיו נשאר לבדוק האם יש אפשרות כזו גם בפוסטגרס, אם כי אולי תעדיף כבר להשאר עם הפיתרון הרגיל, שבכל מקרה זה מה שממומש מאחרי הקלעים....

chagold

@yossiz אמר במניעת race condition ב-DB:

@clickone עזוב טבלאות אחרות, בטעות הזכרתי שיש טבלה של org.
יש רק טבלה אחת של charges עם 3 עמודות (שקשורות לנושא שלנו): id, orgId, chargeNumber
ה-chargenumber אמור להיות מספר ה-charge לפי הארגון. זה לא יכול להיות autoincrement כי אפשר שתיים עם אותו מספר כאשר הם משוייכים לארגון אחר

לא שיש לי פתרון. אבל הרי הפרנציפ נמצא בכל טבלה עם מספור ID אוטומטי, רק שכאן אתה צריך לממש את זה ידנית, אבל מסתמא אפשר להעתיק את המימוש הזה משם?

תחומים - פורום חרדי מקצועי

מניעת race condition ב-DB