Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What happens when there is update to rows (in terms of database internals) Expand / Collapse
Author
Message
Posted Saturday, April 20, 2013 2:09 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:47 PM
Points: 587, Visits: 1,998
Hi,

What happens when there is update to rows (in terms of database internals)?

Thanks
Post #1444742
Posted Saturday, April 20, 2013 4:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
My assumption would be that after the syntax of the DML statement was validated the appropriate rows (based on the where clause) are loaded into memory with these rows being marked so that they're locked from others updating them. The appropriate redo & undo change vectors are generated (does somebody know by what process?) related to this update; the redo & undo being written to the TLog by lazywriter and the undo record is logged in the tempdp. Then the operations are applied in memory and, assuming the update wasnt in a still running transaction then the update is commited & others can see it. Row level locks are removed & the tempdb record is marked as being no longer needed (due to the commit).

Or something is wrong here?

Dird



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1444751
Posted Saturday, April 20, 2013 4:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Close enough.

Dird (4/20/2013)
the redo & undo being written to the TLog by lazywriter


Lazy writer does not do writes to the transaction log.

and the undo record is logged in the tempdp.

Only if the DB is running in one of the row version isolation levels or there's an update trigger (and if there is, it's the previous version, not the undo info)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1444757
Posted Saturday, April 20, 2013 5:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
GilaMonster (4/20/2013)
Lazy writer does not do writes to the transaction log.


Oh lol I meant log writer -_ I should have just said LGWR like i wanted to >_< (http://www.pasteall.org/pic/show.php?id=49671)

and the undo record is logged in the tempdp.

Only if the DB is running in one of the row version isolation levels or there's an update trigger (and if there is, it's the previous version, not the undo info)[/quote]

Ah OK yeah, my answer was very much Oracle focused so I forgot that SQL lets you see uncommited data changes.

Edit: Do you know what process generates the change vectors? :o

Dird



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1444765
Posted Sunday, April 21, 2013 3:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Dird (4/20/2013)
GilaMonster (4/20/2013)
Lazy writer does not do writes to the transaction log.


Oh lol I meant log writer -_ I should have just said LGWR like i wanted to >_<


It's not the log writer process that logs the changes either. It's the process running the update that generates the log records (and they're just the changed columns) and writes it to the log.

Log writer and lazy writer are only concerned with writing already made log records/changes to disk.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1444785
Posted Sunday, April 21, 2013 3:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
GilaMonster (4/21/2013)
It's not the log writer process that logs the changes either.


Log writer and lazy writer are only concerned with writing already made log records/changes to disk.


Is writing it to disk (the TLog) not logging the changes?

Dird



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1444786
Posted Sunday, April 21, 2013 6:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Google: log buffer.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1444789
Posted Sunday, April 21, 2013 6:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
That doesn't address the question >_< but yeah it seems it is the log writer is in charge of the logging~ nothing's in a commited state while its still in memory (i.e. log buffer) until lgwr flushes it out to the TLog.

Dird



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1444790
Posted Sunday, April 21, 2013 7:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Yes it does answer the question.

Process making the changes generates the log records and puts them into the log buffer (logs the changes). Log writer writes the log records to disk before the transaction commits or data pages are written to disk (hardens the log records)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1444792
Posted Sunday, April 21, 2013 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
Hardens the log


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1444798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse