Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-08-18 : 10:33:35
|
| I've written a 1 time batch procedure to update a table. It's several steps and I'd like to prevent INSERT operations by others during the process.Will bracketing the procedure with BEGIN TRAN / COMMIT TRAN suffice to lock the tables or are explicit locks necessary?SamHere's the BOL text that suggests the tables are locked:BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources. |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 10:38:36
|
| Only using TRAN command will allow inserts to occur dutring your transactionI would use XLOCK (Exclusive Lock) |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-18 : 10:48:35
|
| Are you sure? What are the "Locked Resources" mentioned in BOL above?It's tougher than that. I have 3 steps in the procedure. I can't let users access the table during those steps.Worse, the table is renamed and another takes it's place. What of anyone waiting for a lock to be released? Will they get the correct table?Sam |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 11:08:32
|
Here is an example to demonstrate the behavior:Run the following code in a query window:create table t ( v int )insert into tselect 1GObegin tran WAITFOR DELAY '00:00:10' update t with (xlock) set v = v*2 Now open a new window, and within 10 seconds :), run the following:insert into t select 10 because you have specified XLOCK table hint. the insert will not occurbut if you restart the test without the XLOCK hint the insert will occur during the existing transaction. Does that make any sense or help to clarify? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-18 : 11:23:36
|
| Sure it makes sense. But I wonder what BOL meant: resources are locked with Begin tran / commit tran.Thanks for the example. But how would I lock a table accross several T-SQL statements? |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 11:26:39
|
quote:
I just wonder what resources are locked with Begin tran / commit tran
The way I understand it is the only resources which are locked are the resources begin used at that particular instance of the transaction.quote:
But how would I lock a table accross several T-SQL statements
My thought is to specify the hint on every statement |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-18 : 11:30:37
|
quote:
Originally posted by ehorn
My thought is to specify the hint on every statement
Wouldn't the lock be freed between statements? Worst case, perhaps there's a select of an unlocked table between two locked statements. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 20:33:20
|
| Sam,You raised a good point in that last post and while I am no expert I was looking into the following transaction settings (SET TRANSACTION ISOLATION LEVEL -- SERIALIZABLE) which may be what you are looking for, depending on what your default setting are (typically READ COMMITED). Maybe some of the transaction gurus can add some level of expertise to these questions as they are valuable information to understand. |
|
|
|
|
|
|
Table Locks