Robert's approach also lends itself to scalability, where you INSERT/UPDATE in one database and SELECT from others, using MySQL's easy to use replication capabilities.
This also lets you use the very fast MyISAM tables in the slaves for SELECTs and limit transaction locking (if it's even necessary) to INNODB tables on the Master. And you can further speed up the INSERTs/UPDATEs by not indexing the Master, and having the slaves build their own indices rather than replicating them. -- Bill ---------- Original Message ----------- From: Roland Dumas <[EMAIL PROTECTED]> To: [email protected] Sent: Sun, 11 Feb 2007 13:16:39 -0800 Subject: Re: Witango-Talk: Preventing two order submits > On Feb 11, 2007, at 12:54 PM, Robert Garcia wrote: > > > The transaction works, if you use a lock (read/write exclusive) and > > use the select max method, but that is not a good idea on a heavily > > trafficed app. > > yes, locked. and it hasn't failed in 4 years and there are periods > of high traffic and near simultaneous transactions (knocking on > wood).... using mysql here. > > > > If you are using mssql, you don't need to use a transaction, let me > > find the name of that method... > > > > I don't remember the syntax of getting the value, I wouild have to > > look up my code, but what you are looking for is @@scopeidentity, > > not @@identity. Then a transaction is not needed. > > > > @@scopeidentity gets the last identity inserted, for the current > > session/connection. > > > > -- > > > > > > On Feb 11, 2007, at 12:36 PM, Roland Dumas wrote: > > > >> what about creating a transaction, inserting the order, and in the > >> transaction, asking for ... select <@literal @@identity> .. to get > >> the order's record number? You're not going to get the same > >> unique ID for two orders coming in at the same time. > >> > >> On Feb 11, 2007, at 12:26 PM, Robert Garcia wrote: > >> > >>> Do not make the mistake of doing an insert, and asking for the > >>> last ID doing something like select max(rowid) from table. This > >>> works, but it is NOT full proof. If there are 2 orders happening > >>> at the same time, you can get the wrong value, and have 2 > >>> sessions pointing to the same order. So, create a GUID > >>> (guaranteed unique identifier) and either use that as the order > >>> key, or use it to retrieve the order id if you want to use the > >>> nice integer id. > >>> > >> > >> > > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ------- End of Original Message ------- ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
