April 20, 2013 at 2:09 pm
Hi,
What happens when there is update to rows (in terms of database internals)?
Thanks
April 20, 2013 at 4:12 pm
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
April 20, 2013 at 4:55 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2013 at 5:42 pm
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? 😮
Dird
April 21, 2013 at 3:39 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2013 at 3:46 am
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
April 21, 2013 at 6:34 am
Google: log buffer. 🙂
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
April 21, 2013 at 6:42 am
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
April 21, 2013 at 7:08 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2013 at 8:28 am
Hardens the log :w00t:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply