Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Hibernate - Stored Procedure -turn off auto commit and i get a lock on a table.  RSS feed

 
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi guys

I hope you can help me here. My underlying database is Microsoft SQL Server. This by default has auto commit in place

from hibernate i am calling a stored procedure which inserts and updates rows in a few different database tables. I call the stored proc like so:


I know this may not be the right way. But it works. The data commits right away. thats my issue. I do not want autocommit in place as I want to carry out further data manipulation under the one transaction
What I need to do is query the database for the changes the stored proc carried out
Query query=session.getNamedQuery("myNamedQuery");
(List<StoredProcObjects>)query.list();
and carry out further updates on the objects. At this point I want to commit everything or rollback if i hit an issue.

To avoid autocommit I have set in the stored proc



And in my code I have attempted to set the connection like so


When i do these the data does not autocommit but calling

freezes as the table(s) are locked as the stored proc has not yet committed. I need to be able to do a dirty read and do further changes on the dirty data.

Any help here is much appreciated. Everything works fine if i leave autocommit in place but this is dangerous as the stored proc could commit data and the extra updates fall over. DOing all the work in the stored proc is not an option

Thanks
Alan
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!