6

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.

Improve this question
3
  • 2
    This may be a stupid question, but do you actually commit the transaction at the end?
    – mclark1129
    Sep 30, 2011 at 19:03
  • @MikeC: I was wondering the same thing myself.
    – MyItchyChin
    Sep 30, 2011 at 19:20
  • @MikeC Yes, I do.
    – RPK
    Oct 1, 2011 at 4:11

4 Answers 4

Reset to default
4

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.

Improve this answer
3
  • I am not following why the other user can't even use a SELECT?
    – RPK
    Sep 30, 2011 at 15:42
  • 2
    @Bart: This is not always true, the transaction isolation level can be changed.
    – Massimo
    Sep 30, 2011 at 17:39
  • 2
    no other data can be written to the table until you end your transaction This is false. SQL Server has had page-level and row-level locking for a very, very, long time.
    – Brandon
    Aug 31, 2015 at 12:44
4

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

Improve this answer
4
  • 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, 2011 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, 2011 at 0:58
  • I don't believe read uncommitted will prevent an insert/update/delete from placing locks. Isolation level may influence the types of locks placed (row-level, row range, page, etc.) and which types of locks it will honor (will it block on somebody else's shared lock or not). But I don't believe that any of the basic 4 isolation levels will avoid locking updates.
    – Brandon
    Aug 31, 2015 at 12:47
  • However, there is snapshot isolation, which another answer mentioned, which looks sort of like another isolation level, but is really a way to keep readers and writers from blocking each other.
    – Brandon
    Aug 31, 2015 at 12:48
3

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

Improve this answer
1
  • You mention Oracle's default isolation level is read committed - is that for all versions?
    – Peter Schofield
    Nov 6, 2011 at 22:44
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .