Re: Oracle Vs. SQL Server on NT

On Mon, 23 Mar 1998 16:22:59 GMT, nuno-v-guerreiro_at_telecom.pt (Nuno Guerreiro) wrote:

I've only just started working with SQL Server but..

>I'm curious about Microsoft's way of controlling concurrency. I've
>never worked with SQL Server 6.5. What does exactly "page-level
>locking" mean? Does it mean that whenever I'm updating a table record,
>the entire block in which it resides gets locked?
>

Yes.

In addition, read commited isolation is implemented by preventing reads of uncommited data (according to what I've read) - a page cannot be read by another user if changes to it have yet to be commited. Likewise, a page that is currently being read cannot be updated. Unlike Oracle, readers block writers and vice versa. If too many locks are taken out on a table, the lock is escalated to table level, so only one user can update the table.

This is not a big problem if you design the application to cope with it by keeping transactions very short and not leaving cursors open etc.

It is very common when working with Oracle to open a cursor and fetch only the first few rows for presentation to the user, leaving the cursor open to move forward as required. This would be a very bad practice in SQL*Server, I think.

SQL Server works this way becuase locks are kept in memory. I believe Oracle writes lock information to the data blocks involved, meaning that it can cope with an indefinitely large number of row locks, at the cost of a significant performance penalty when compared with SQL Server.

Converting a database from SQL Server to Oracle would be quite easy. Converting from Oracle to SQL Server is much more problematic, because of the risk of severe lock contention. I imagine this is why the applications talked about in another message are not available for SQL Server.

>Another issue:
>
>How does SQL Server 6.5 handle isolation between transactions? Which
>of the following levels of isolation defined by the SQL standard is
>the default?
>
>. Read uncommited
>. Read commited (Oracle's default)
>. Repeatable read
>. Serializable
>

By default, SQL Server defaults to read commited, but also provides read uncommited and serializable. If I remember aright, Oracle provides only read commited and serializable. Received on Mon Mar 23 1998 - 00:00:00 CST