מניעת race condition ב-DB

[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 אוטומטי, רק שכאן אתה צריך לממש את זה ידנית, אבל מסתמא אפשר להעתיק את המימוש הזה משם?

[avr416]

לא הבנתי למה לא נעילה ברמת הקוד?
גם את הנעילה הזאת אתה מקבל בחינם מהקוד..
ולגופו של ענין, הרי אתה לא ניגש לdb מחוץ לקוד, אז אני לא רואה סיבה למה לא להשתמש בזה.
אתה גם יכול לעשות נעילה בקוד פר ארגון, כך שלא יהיו לך הרבה התנגשויות.

אני מממש דבר כזה בחנות אינטרנטית, כשיש צורך להגביל מוצר לפי כמות במלאי, ואני לא רוצה שיווצר מצב דומה למה שתיארת.

[yossiz]

@chagold אבל הקוד הפנימי של ה-DB שמממש את זה לא זמין לנו המשתמשים.

[chagold]

@yossiz https://www.postgresql.org/docs/9.3/sourcerepo.html?

[yossiz]

@chagold אני יודע שקוד המקור של ה-DB זמין, אבל מה אני אעשה עם זה?
אני לא הולך לערוך את הקוד ולקמפל כדי להוסיף אפשרות זו.

[chagold]

פוסט זה נמחק!

[yossiz]

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

4. לעשות את כל התהליך בפקודת SQL אחד.

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

אני יודע שבSQL SERVER נכון להיום אתה מוגן ב4

אתה בטוח בזה? יש לך מקור?

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

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

מכיון שהנושא מאוד סקרן אותי והתחלתי לחשוש שזה לא נכון עשיתי נסיון,

• יצרתי טבלה פשוטה:

create table test (id  SERIAL, i INTEGER);

ואז הרצתי בלולאה במקביל בשתי סשנים את הפקודה הזאת:

INSERT INTO
    test (i)
VALUES
    (
        (SELECT max FROM (SELECT MAX(i) AS max, pg_sleep(1) FROM test) AS i) + 1
    );

(הוספתי את ה-pg_sleep כדי למקסם את הסיכויים להתנגשות)

התוצאה היתה שהיו מספר שורות עם אותו מספר, בניגוד לצפיה שכל שורה תהיה מספר אחד מעל לשורה הקודמת, כלומר שהיה race condition.
אח"כ שניתי את הפרמטרים של הקליינטים:

SET default_transaction_isolation TO serializable;

והרצתי את זה שוב, וחלק מהפעולות נכשלו בגלל התנגשויות

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

אני חושש שזה המצב בכל המנועים ואם כן זו נקודה חשובה להיות מודע לזה.

[dovid]

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

אתה בטוח בזה? יש לך מקור?

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

[yossiz]

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

סתם ככה אשמח לדעת מה הרקע של הצורך.

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

[yossiz]

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

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

גם אני לא הבנתי. כנראה שהעונה לא הבין את השאלה.

[clickone]

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

אין באמת צורך כל כך חשוב

יש דברים שזה חשוב עד קריטי, לדוגמא מספר חשבונית / קבלה ששם אסור כפילות

[מנצפך]

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

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

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