I wrote a stored procedure to insert a record. I added "Begin Transaction" just above the Insert Query and execute the query. I noticed that another application that was showing a web page with records from the same table, hanged by the time the insert completed.
Why Begin Transaction locks the entire table? Writers should not block Readers. It should be ON by default.
I am using SQL-Server 2005 Express. I also want to know how Oracle and MySQL handles the same situation.
Best Answer
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.