Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
2  
This may be a stupid question, but do you actually commit the transaction at the end? –  Mike C Sep 30 '11 at 19:03
    
@MikeC: I was wondering the same thing myself. –  MyItchyChin Sep 30 '11 at 19:20
    
@MikeC Yes, I do. –  RPK Oct 1 '11 at 4:11
add comment

4 Answers

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.)

share|improve this answer
    
You mention Oracle's default isolation level is read committed - is that for all versions? –  Peter Schofield Nov 6 '11 at 22:44
add comment

Writers should not block Readers

This is true only for snapshot isolation, all other isolation levels require both readers to block write blocker and writers to block readers (dirty reads not considered, since they are inconsistent and should never be used). If you need this behavior, then use row versioning (the link contains the solution).

Why does bulk insert lock the entire table?

This actually may or may not be true. The behavior is under your control:

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk-import operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load.

For more details, read the product specifications: Controlling Locking Behavior for Bulk Import.

share|improve this answer
add comment

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

share|improve this answer
    
Reading the docs it seems that not only can other transactions modify data out from under the transaction but the reverse is also true permitting dirty read/writes both by the transaction and by other transactions. Please tell me I'm reading this wrong. "Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction." –  MyItchyChin Sep 30 '11 at 19:24
2  
If you are running a transaction at READ UNCOMMITED level, other users can read the data you're updating. They can't modify them. –  Massimo Oct 1 '11 at 0:58
add comment

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.

share|improve this answer
    
I am not following why the other user can't even use a SELECT? –  RPK Sep 30 '11 at 15:42
1  
@Bart: This is not always true, the transaction isolation level can be changed. –  Massimo Sep 30 '11 at 17:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.