Problem: After I make use of an auto-incrementing primary type in my database, this occurs constantly:
I wish to store a purchase with 10 Products. The purchased Products fit in with an order. And So I keep order, request the database during the last placed id (that is harmful if this involves concurrency, right?), after which keep 10 Products using the foreign key (order_id).
And So I always need to do:
last_placed_id = db.lastInsertId()
Place ... Place ... Place ...
but jetski from me by using transactions in just about all Place cases when I want an overseas key.
So... here some solutions, and I'm not sure if they are great:
A) Avoid using auto_increment secrets! Make use of a key table?
Key Table might have two fields:
table_name, next_key. Each time I want a vital for any table to place a brand new dataset, first I request for the followingcrucial by being able to access a unique static KeyGenerator class method. This may a Choose as well as an UPDATE, if at all possible in a single transaction (would that actually work?). Obviously I'd request that for each affected table. Next, I'm able to Place my entire object graph in a single transaction without playing ping-pong using the database, before I understand the secrets already ahead of time.
B) Using GUUID / UUID formula for secrets? These suppose to become really unique worldwide, and they are LARGE. I am talking about ... L_A_R_G_E. So a large quantity of memory would get into these gigantic secrets. Indexing is going to be hard, right? And data retrieval is a discomfort for that database - a minimum of I suppose - integer secrets tend to be faster to deal with. However, these offer some security: Site visitors can't iterate any longer total orders or all customers or all pictures just by incrementing the id parameter.
C) Stick to auto_incremented secrets? Ok, if then, how about transactions like referred to within the example above? How do i solve that? Maybe by placing a Ghost Row first after which doing an transaction with one UPDATE + n Card inserts?
D) What else?
When storing orders, you need transactions to avoid situations where only half your items are put into the database.
Based on your database as well as your connector, the worthiness came back through the last-place-id function may be transaction-independent. For example, with MySQL,
mysql_insert_id returns the identifier during the last query from that specific client (without struggling with the other customers are doing at the same time).
Which database are you currently using?
Yes, typically placing an archive after which attempting to choose it again to obtain the auto-produced secret is bad, especially if you work with a naive choose max(id) from table query. The reason being the moment two threads are coming up with records max(id) might not really return the final id your present thread used.
One method to avoid this really is to produce a sequence within the database. Out of your code you choose sequence.NextValue then use that value to then execute your card inserts (or craft a far more complex SQL statement that performs this selection and also the card inserts all at once). Sequences are atomic / thread-safe.
In MySQL you are able to request during the last placed id in the execution results that we believe will invariably provide you with the correct answer.
Sql Server supports SCOPE_IDENTITY (Transact-SQL) that ought to take proper care of your transaction problem and concurrency problem.
I'd say stick to auto_increment.
(Presuming you're using MySQL)
"request the database during the last placed id (that is harmful if this involves concurrency, right?)"
If you are using MySQLs last_place_id() function, you simply see what went down inside your session. Making this safe. You mention ths:
I'm not sure what framework or language it's, however i would think that uses MySQL's last_place_id() underneath the covers (otherwise, it's a pretty useless database abstraction fromework)
" In my opinion jetski from me by using transactions in just about all Place cases w"
I do not understand why. Please explain.