Win a copy of Penetration Testing Basics this week in the Security forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Transaction or Table Locking?

Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi List People,
I'm starting an app which I would like to make database-independent, e.g. so that it can work on Oracle or mySQL. I would like to have one schema that works on both, no stored procedures and, well that's challenging enough.
My first issue: primary keys
I'll have an extra helper table (last_key_used) to store the last primary key used in each table. When I insert a new row in a normal table, I'll do this:
get last value from last_key_used table
increment it
update last_key_used table with incremented value
use the incremented value for the primary key in my new row
I want to try transactions - so that I can place the whole operation in a transaction - and alternatively I want to try just table locking. But I don't know how to do either in Java.
Can anybody give me a pointer for transactions?
Also any ideas how I could use table-locking, i.e. how do I get 4 SQL statements to execute in one block and return a value? (LOCK TABLE; SELECT; increment the value; UPDATE; UNLOCK;)
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic