Implicit commit & DDL – Oracle vs Ms Sql Server

I start speaking about the winner for me:

This time the winner is ….. Microsoft Sql Server (this time).

Why ?

Because Oracle issues an implicit commit before and after any DDL (alter, create, truncate, etc.), even if in a transaction block (begin … commit/rollback).

Sql Server will commit the DDL only after statement execution (in autocommit mode), and in presence of an explicit transaction will not commit the DDL after the statement !!!

Sql server will log in his transactions log the pages involved in a truncate operation for example giving you the possibility to rollback in a consistent way the entire transaction.






Primary Key violation on peer to peer replicated identity column in Ms Sql Server – one solution without range definition

There is a big issue replicating identity column on SQL server in a peer to peer topology:

The “seed” on the identity column of the replicated table remains in the past, causing primary key violation (pk is set on the identity column) if a user try to insert a row.

Microsoft suggests some solutions:

I have also found various approaches and issues about this:

but we needed something that would preserve the identity value in all tables without touching the application code.

We do not want to partition the range as suggested, to have a continuous sequence.

It would be easy with 2 table replicating, on the second one do the following command:


So, you can easily realize the next value for identity using the command:


As said you can realize that there is a problem, first table seed has 4564, but current value (due to transparent replication) is 4575


Checking identity information: current identity value '4564', current column value '4575'.
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 Checking identity information: current identity value '4575', current column value '4575'.
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.</pre>

Inserting a row in [DATABASE_MASTER].[dbo].[TABELLA], would cause the pk violation on identity because the column already has joined the value of 4575 and next calculated value (4564) is old.


Create a trigger “before insert” (in Oracle exists, but this can be done in Sql server with a simple workaround), implementing RESEED operation, just before the insert itself:


Checking identity information: current identity value '4575', current column value '4575'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '4575', current column value '4575'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This will reset the counter for the local table even if replication with the clause NOT FOR REPLICATION on identity column has incremented the value.

The instead of insert trigger has to make the insert !!! So use insert from inserted with all columns except the identity one.

CREATE  TRIGGER [dbo].[REPLICATED_TABLE_update_identity]
-- DBCC CHECKIDENT('[dbo].[prf_PROFILES]', RESEED) WITH NO_INFOMSGS; no rows returned in debugging
-- next three lines for debugging purpose
declare @myCurrentSeedValue int;
select @myCurrentSeedValue = IDENT_CURRENT( '[dbo].[TABELLA]');
PRINT @myCurrentSeedValue;
-- Insert statements for trigger here
-- identity column is not inserted, you have to set SET IDENTITY_INSERT <TABLE_NAME> ON
insert into TABELLA
( [col2]

I did not find this solution anywhere, so I hope we will not have troubles ;-)).

Identity column should not be used on client server application, application should use sequence (best practice) as in Oracle which introduced identity column only in last 12c release.

01.04.2016 UPDATE

There is a known issue with Entity Framework (ORM) and probably with NHYBERNATE:

You may loose the scope_identity returned value, after the insert because of the “instead of insert” trigger

SCOPE_IDENTITY() gives you the last IDENTITY inserted in the scope of your statement, e.g. on the table(s) that your own, actual statement referenced

A workaround is to create a temp table in the calling procedure where the insert is being performed, insert the scope identity into that temp table from inside the instead of trigger, and then read the identity value out of the temp table once the insertion is complete.

Deadlocks in Microsoft Sql Server, some quick resolutions and Oracle comparison

First: what is a deadlock ?

A very simple, nice, and general explanation can be found here:

To find deadlock with Sql Server Profiler (there are other methods: extended events, etc.):

In Sql server select statements will lock rows that are selected (during the statement) to ensure transactional reliability. So it is not so difficult to have a deadlock. Sql server lock behavior can be changed, you can tell him to ignore locks with the use of the “hint” in your statements: NO LOCK (this is the same to use READ UNCOMMITTED transaction isolation level, this will implies dirty reads, the default is READ COMMITTED). So dirty reads means that you can get inconsistent results: read a value that will be rolled back, a value that will be updated by another transaction, etc. You can use the hint: READPAST, ignoring only locked items (not locks), in this case the inconsistent result is that you can read a value non yet committed. From Sql Server 2005,

there is a very good solution without touching the code: you can use “SET TRANSACTION ISOLATION LEVEL SNAPSHOT” (multi version concurrency control, that is the corresponding of “row versioning” in Oracle), and you will avoid select locks, but at the same time you will have consistent transaction. This is great, but it will add overhead (It uses temp db to store “versioning”) to support this very nice feature, that you have to allow with this first command:


so you can make snapshot read committed the default
and you will NO NEED to add the SET TRANSACTION ISOLATION LEVEL SNAPSHOT in the code.

this will make minimal or eliminate deadlock probability !

For details and confirmation:

The Difficulty with Deadlocks

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide (video)

NOTE: remember that two select statements (on same rows from two different transaction) cannot make a deadlock in sql server, but select AND insert/update/delete/ecc YES, this is because select statements issue shared locks and two shared locks are compatible so no deadlocking will be generated in this case.

Application cause: Remember that good design on the order of operations is the primary approach to avoid deadlock.

Index cause: Sometimes Sql Server deadlock is caused by key lookups operation on the target table. Creating or modifying existing index will avoid this kind of situation, take a look to this article, to go deep:

Locking in Microsoft SQL Server (Part 18) – Key lookup deadlock

Deadlocks caused by Missing Indexes in SQL Server

Cursor nightmare: Avoid using too much cursors, that’s the reason:

Long transactions: keep transactions quick (not many actions during transaction for example involving temporary objects etc.) and small (involving few portions of data).


Oracle deadlock

Oracle has a big thing, that Sql Server does not have: rollback segments (UNDO). Data have to be committed before writes. Oracle does not lock rows for select statement !!! Other transaction will look before image data in rollback segments (old fashion name), so deadlock is a rare situation.

To know all about deadlock causes for Oracle I would suggest this great article by Arup Nanda:

and my post here, with some more detail about block contention deadlock and solution:

Remember that first cause of deadlock is bad application and database (normalization) design also for this DBMS.



What to do when you need to have multiple gateway on a client

When you set a gateway address on your windows machine for example, what happens ?

The windows machine adds a default route to the machine route table.

If a packet is destinated to a different subnet (from mine), it is sent to the gateway. The default gateway can be a router (even a layer 3 switch, a firewall, ecc.).

But what happens if I have two gateway on the network ? For example two router, one connecting to the Internet and the other one connecting to other corporate locations.

There are many solutions (at least 4):

1) tell the gateway of the workstation (the first router of the example) to forward the packets to the second gateway (second router), with a route. Remember to set the route to go back on the client, on the second router (if it does not know about the source network). The command on CISCO (for the route “to go”) is simple, here is an example (router is the second router):

ip route (forward all packet to the class A network 10.x.x.x to gateway)

or for just a server (referring to picture)

ip route

You can avoid static route sometimes (if you are not the administrator of a router) assigning directly the ip to an interface of the router/firewall and NAT it. But this is a particular  situation.

So keeping the static route (for single server) and natting the ip on the second router (see picture):

ip nat inside source static (natting with on a Cisco Router with inside and outside interface)

or (this is nice):

ip nat outside source static

In this scenario (more complex) I have sent packets to the router1 instead of the pc owner of the ip (with ip route command), and natted the ip for a server of the network on router 1 inside interface.



Even if there is a PC with connected to router zero, it will be ignored, because frame will have target destination the outside interface of router 1.

The “go back” route on router 1:

ip route

2) The second method is the most simple: set a static route on the workstation, typical sintax is:

route add <destination> <mask> <other_gateway> <metric>

3) This is very nice: you can configure the gateway address of each workstation with the same IP address of the
workstation itself. The workstation will issue an “ARP”, sending a packet on same subnet and not. If a gateway router has a route to that destination (and it is configured for Proxy ARP), it will respond with the MAC address for its’ own interface. The workstation will send the packet to that router. ARP broadcast traffic is increased, with this solution.

4) The fourth option is to segment your LAN using a layer-3 switch. Divide the large LAN into smaller segments, each with its own subnet. The gateway routers can be placed on a separate subnet from your servers and workstations so that there is, only one gateway (the layer-3 switch VLAN interface) for all.

First two are most used, and most easy solutions for this problem. The third is nice, but ARP broadcast can create performance problem, the 4th is just a little bit expensive (money), but is the best solution.







Hint or do not hint in Oracle ?

What are hints ? Optimizer hints can be used with SQL statements to alter execution plans. Hints let you make decisions usually made by the optimizer.

So using hints, you want to be “the optimizer”. You might know that a certain index is more selective. So you might be able to choose a more efficient execution plan than the “real” Oracle optimizer. In such a case, use hints to force the optimizer to use what you think is the optimal execution plan.

Example: select /*+ index(customer cust_primary_key_idx) */ * from customer;

SO THE QUESTION IS: USE HINT OR NOT IN YOUR QUERIES ? My opinion after reading and reading over books and internet is DON’T USE !

Of course we are not speaking about hints used for controlling parallelism, append operation (direct path), ecc. which can be considered directives rather then “plan force” statements.

I will just write here what Jonathan Lewis thinks about hinting:

 Rules for Hinting

  1. Don’t
  2. If you must use hints, then assume you’ve used them incorrectly
  3. On every patch or upgrade to Oracle, assume every piece of hinted SQL is going to do the wrong thing. Because of (2) above; you’ve been lucky so far, but the patch/upgrade lets you discover your mistake
  4. Every time you apply some DDL to an object that appears in a piece of hinted SQL assume that the hinted SQL is going to do the wrong thing. Because of (2) above; you’ve been lucky so far, but the structural change lets you discover your mistake

Now, who is Jonathan Lewis and do you think to know more then him about Oracle optimizer ?

“If you think you know more about hints than Jonathan Lewis seek professional help from a licensed psycho therapist. Advice, unfortunately, that I would give to almost everyone that has ever posted a hint in an example on the web.”

Daniel Morgan – Oracle ACE Director

See also this article with document by Jonathan Lewis:

Jonathan Lewis has been working in the information technology industry for nearly 25 years, and has been using the Oracle relational database management system for more than 20. For the past 16 years, he has worked as a freelance consultant, often spending only one or two days at a time with any client to address critical performance problems. He also advises on design and implementation problems, and on how to make best use of the most appropriate Oracle features for a given project. Jonathan is also renowned throughout the world for his tutorials and seminars about the Oracle database engine and how to make best use of it. Having visited 42 countries at last count, his exceptional ability has earned him an O1 visa from the United States, allowing him to do consultancy and lecture work there. Jonathan has written two books about Oracle (Practical Oracle8i, Addison-Wesley, 2000; Cost-Based Oracle Fundamentals, Apress, 2005), and has contributed to two others (Oracle Insights, Apress, 2004; Oracle Database 10g New Features, Oracle Press, 2004). He also writes regularly for the UKOUG magazine, and occasionally for other publications around the world. In the limited amount of time he has leftover, Jonathan also publishes high-tech Oracle articles on his blog at

From “Oracle Performance Survival Guide” (read by me) by Guy Harrison (one of the author of Dell tool Spotlight ) page 21: Jonathan Lewis is probably the best-known independent expert on CBO. His book “Cost Based Oracle Fundamentals” (Apress, 2006) is essential reading for anyone who wants to understand CBO internals