web analytics
Implicit COMMIT After DDL Statement Options
codeling
Posted: Thursday, October 12, 2017 3:27:42 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 892
Points: 3738

Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement.

Let's create a table in Oracle and insert a row:

Oracle:

  -- Create a table and insert a row
  CREATE TABLE states
  (
      abbr CHAR(2),
      name VARCHAR2(90)
  );
 
  -- Transaction will be in progress after this insert
  INSERT  INTO states VALUES ('CA', 'California');

Now let's create another table and perform ROLLBACK operation:

Oracle:

  -- Create another table table and insert a row
  CREATE TABLE cities
  (
      name VARCHAR2(90),
      state CHAR(2)
  );
 
  INSERT INTO cities VALUES ('San Francisco', 'CA');
 
  ROLLBACK;

You can see that even after ROLLBACK, the table states and row in this table exist because CREATE TABLE cities statement committed the transaction.

Table cities also exists, but the inserted row was rolled back:

Oracle:

  -- Table states exists and contains 1 row
  SELECT COUNT(*) FROM states;
  -- Result: 1
 
  -- Table cities also exists, but the inserted row was rolled back
  SELECT COUNT(*) FROM cities;
  -- Result: 0
Sponsor
Posted: Thursday, October 12, 2017 3:27:42 PM
 
codeling
Posted: Thursday, October 12, 2017 3:29:05 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 892
Points: 3738

By default, SQL Server works in AUTOCOMMIT mode so COMMIT is issued after each statement, but when a explicit transaction is started, SQL Server does not issue a COMMIT after a DDL statement:

Let's start a transaction, create a table in SQL Server and insert a row:

SQL Server:

  -- Start a transaction explicitly
  BEGIN TRANSACTION;
 
  -- Create a table and insert a row
  CREATE TABLE states
  (
      abbr CHAR(2),
      name VARCHAR(90)
  );
 
  INSERT  INTO states VALUES ('CA', 'California');
  -- 1 row(s) affected

Now let's create another table, insert a row and perform ROLLBACK operation:

SQL Server:

  -- Create another table table and insert a row
  CREATE TABLE cities
  (
      name VARCHAR(90),
      state CHAR(2)
  );
 
  INSERT INTO cities VALUES ('San Francisco', 'CA');
  -- 1 row(s) affected
 
  ROLLBACK;

You can see that after ROLLBACK, none of the tables exists:

SQL Server:

  SELECT COUNT(*) FROM states;
  -- Msg 208, Level 16, State 1, Line 1
  -- Invalid object name 'states'.
 
  SELECT COUNT(*) FROM cities;
  -- Msg 208, Level 16, State 1, Line 1
  -- Invalid object name 'cities'.
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.