Why "Begin Transaction" before "Insert Query" locks the entire table?

Begin Transaction is the start of a transaction - no other data can be written to the table until you end your transaction, this is by design, so as to enforce the ACID criteria on the database. http://en.wikipedia.org/wiki/ACID

You use a transaction if you need to execute multiple queries as if they were one single atomic operation. If you don't need atomicity, don't use transactions!

This is very very basic stuff though - you may need to freshen up on basic database theory before trying to write database code, you could do serious damage to an app if you're not familiar with core principles like transactions.

Although what others have said is basically correct, this behaviour depends on the transaction isolation level you are using; it is technically possible for a transaction to not lock an entire table.

If you want other users to be able to read your data while you are still modifying them, you can set the TIL to READ UNCOMMITED:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

Of course, you need to be very careful with this setting, as it can cause any sort of data inconsistencies, depending on what other users are doing at the time.

More info here:

http://msdn.microsoft.com/en-us/library/ms189122.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx

Well, I'm not a SQL Server guy, so, I won't speak to that.

But, since the question is tagged 'oracle' and the poster also asked how it works in Oracle, I'll address that portion.

In Oracle, the default transaction isolation level is READ COMMITTED. Further, Oracle always locks at the row-level, and never escalates a lock to the block (called page in SQL Server?) level.

So, if you lock row 'a' in a table, and then another session attempts to lock row 'b' in the same table, that lock will succeed, even if the rows are in the same block.

As to "beginning a transaction", in Oracle, any DML will implicitly begin a transaction, if one has not already begun. That transaction will remain open until the session explicitly commits or rolls back, or is killed (in which case Oracle will roll back the transaction).

However, Oracle does have a "begin transaction" syntax, which is 'set transaction'. This can be used to begin a read only or read write transaction,or to set isolation level.

One interesting use of a read only transaction is that (in addition to being read only), it provides all queries that execute in that transaction, read consistency to the point in time of the start of the transaction. So, once you do 'set transaction read only', any query you execute will return results consistent with the time of the start of the transaction. (Normally, query results are self-consistent with the point in time of the start of query execution, but with a read only transaction, all queries executed in the transaction will be consistent with the start of the transaction.)