The AUTO_PK_SUPPORT table exists only to support PK generation for DBs that do not have native support for it built in. However, in this day and age any database worth using has this built in. I've run production apps with Postgres, MySQL, FrontBase, and Interbase / Firebird, and all of them have native PK generation. Heck, even Derby, H2, and HSQLDB support it.
The main advantage of a native solution is that you know it works. If you ever used native PK generation the AUTO_PK_SUPPORT table feels very kludgy. It works (I guess?), but it's not ideal. A native solution is faster and simpler. It handles transactions better. Most DBs (all?) do PK generation outside of transactions, so incrementing the sequence always persists regardless of transaction rollbacks or commits. Performance is better because a native DB implementation is able to implement it with as little locking as necessary, which is certain to be much faster that the equivalent set of SQL commands. Another advantage is being able to use the same PK generators to insert data directly with SQL, which is sometimes necessary. Also, maintenance is easier since there are simple built in functions to query or manipulate the sequence value. MySQL's implementation is different than the others I've used. There's is more magical. The component parts aren't accessible to developers. The engine just magically chooses the next highest PK value when rows are inserted without giving any idea where this number comes from or how it is chosen. This has some benefits since the PKs don't have to be explicitly generated ahead of time. I don't know if it's better, but it's different anyhow. On Wed, Dec 2, 2015 at 3:30 AM Aristedes Maniatis <[email protected]> wrote: > I've always used the AUTO_PK_SUPPORT approach to PK generation, all the > way back from WebObject EOF days many many years ago. > > However a recently discovered bug in this approach [1] and how it > intersects with transactions may require use to think about going over to > database generated primary keys with a auto-increment column. For > developers not using an ORM this is pretty common I understand. > > What are the downsides to that approach? Is performance, reliability and > everything else just the same? What problem was AUTO_PK_SUPPORT originally > designed to solve? > > Cheers > Ari > > > [1] https://issues.apache.org/jira/browse/CAY-2040 > > > -- > --------------------------> > Aristedes Maniatis > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A >
