example;

begin tran

Update A ..... where .....

update B .....  where .......

Update C ......  where ....

commit tran

According to the example, i want to ask that, while updating C table,

does transaction still hold locks on the updated rows on A table ? Or Locks had been released before updating C Table?

I have searched forms and could not get satisfying answer.

does transaction still hold locks on the updated rows on A table ? Or Locks had been released before updating C Table?

The locks on modified rows for all tables are held for the duration of the transaction.  This ensures data integrity by preventing other users from retrieving or changing data modified by the transaction.

Hi,

As to my knowledge, the transaction will not release the locks (EXCLUSIVE, UPDATE LOCKS) until it reaches COMMIT in order to satisfy the ACID properties. If your are using Serializable isolation level, it will not allow the transaction to release SHARED lock on database objects right away after complettion of the statement execution where as the other isolation levels allows to release the SHARED lock once the SELECT statement complets its execution in a transaction.

The description above (Chenchi) is accurate except for update locks. They are not held until end of transaction. Either the row doesn't qualify for a modification and the lock is released (while for instance seeking for qualifying rows using an index), or the row qualifies and the update lock is released and replaced by an exclusive lock.