November 28, 2012 at 2:18 am
In books online it says DELETE & UPDATE always grab an ex table Lock.
Im trying to figure the reasoning for this but can anyone advise on the definitive reason for this?
By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.
An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify data. You can specify table hints to override this default behavior for the duration of the UPDATE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. For more information, see Table Hints (Transact-SQL).
November 28, 2012 at 2:50 am
Essentially it is to be able to ensure that all database transactions can be processed reliably. It sticks to the ACID concept (atomicity, consistency, isolation, durability).
Below shamessly copy/pasted from wikipedia (http://en.wikipedia.org/wiki/ACID)
Atomicity
Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
Consistency
The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof.
Isolation
The isolation property ensures that the concurrent execution of transactions results in a system state that could have been obtained if transactions are executed serially, i.e. one after the other. Each transaction has to execute in total isolation i.e. if T1 and T2 are being executed concurrently then both of them should remain unaware of each other's presence
Durability
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter).
I hope this explains,
B
November 28, 2012 at 2:58 am
They don't always grab an exclusive table lock, they can and do lock at row, page or table depending on the rows involved, the available lock memory and a whole bunch more things.
The books online entry does not say that they take an exclusive table-level lock. Just that they take an exclusive lock on the table that the rows involved are in. That exclusive lock taken can be any granularity.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply