• 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:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

SQL Autocommit DDL/DML

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

as far as I know DDL commands such as an alter/drop table are by default setted to autocommit on a RDBMS; on the other hand DML need an explicit commit operation.

Can someone confirm that assumption or explain me if some RDBMS have a different behaviuor?

Thanks
 
Bartender
Posts: 20838
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, while it might vary depending on which DBMS you're using, I've always seen DDL auto-commit. And actually, having to explicitly commit DDL sounds a bit problematic to me.
 
Kirk James
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Actually, while it might vary depending on which DBMS you're using, I've always seen DDL auto-commit. And actually, having to explicitly commit DDL sounds a bit problematic to me.



What about DML? no auto-commit by default? Never worked on the DBMS settings, probably that behaviour can be managed.
 
Tim Holloway
Bartender
Posts: 20838
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You confused me. I call "DML" SQL. Since basically, DDL is the part of "SQL" that isn't actually SQL and SQL is the part that was defined by Codd and Date.

Anyway, as I recall, auto-commit is on by default with MySQL and PostgreSQL and probably Oracle, SQL Server and DB2.

Realistically, saving stuff up to do a commit is something more common to programming than for just fiddling around with the database manually, so I'd expect auto-commit to be the default in general.

But I don't think that it's actually mandated anywhere. For that matter, I don't think that there's a standard that says a DBMS even has to have a command line interface application. Although it's going to be a lot less fun tweaking things without one.

 
Kirk James
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:You confused me. I call "DML" SQL. Since basically, DDL is the part of "SQL" that isn't actually SQL and SQL is the part that was defined by Codd and Date.

Anyway, as I recall, auto-commit is on by default with MySQL and PostgreSQL and probably Oracle, SQL Server and DB2.

Realistically, saving stuff up to do a commit is something more common to programming than for just fiddling around with the database manually, so I'd expect auto-commit to be the default in general.

But I don't think that it's actually mandated anywhere. For that matter, I don't think that there's a standard that says a DBMS even has to have a command line interface application. Although it's going to be a lot less fun tweaking things without one.



About the DML, I use to define DML as Data Manipulation Language, so everything related to your database manipulation (INSERT, DELETE, UPDATE), then DDL as Data Definition Language, everything related to the definition of your data architecture (ALTER, DROP). When you say that:

DDL is the part of "SQL" that isn't actually SQL

sorry but,  I got confused.

Thank you for your reply.
 
Bartender
Posts: 1027
18
Mac OS X IntelliJ IDE Oracle Spring VI Editor Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using transactions with data definition language SQL statements is vendor dependant.   Most don't support them, since you're typically altering the internals that support the actual schema definition.  

The advantage of being able to do so is so you can script your schema upgrade and know that it's either been completely successful or not.  

Here's an article that discusses this; Why You Should Care That Your SQL DDL is Transactional

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!