November 1, 2007 at 6:20 am
I'm studying for my upgrade cert and ran into a few things that I'd either never heard of before or that I didn't understand the explanation of. Any help you can give me on "what it is" and "how it works within the engine" would be greatly appreciated!
The two I can't find definitions for are Exclusive_Transaction_Workspace and Shared_Transaction_Workspace. I've never heard of these before. The book I'm reading doesn't define them as anything except "Entities that can request locks". What are they and how do they work with transactions?
The one I don't have a good "How it works" definition for is Latches. Obviously these are different from locks, but the books I'm reading gloss over the technical details.
Thanks in advance!
November 1, 2007 at 10:26 am
Can't find anything in the books I have access to right now except this from Kalen Delaney's Inside SQL Server (the storage engine) book:
SQL Server provides two separate locking systems. The first system affects all fully shared data and provides row locks, page locks, and table locks for tables, data pages, LOB pages, and leaf-level index pages. The second system is used internally for index concurrency control, controlling access to internal data structures and retrieving individual rows of data pages. This second system uses latches, which are less resource intensive than locks and provide performance optimizations. You could use full-blown locks for all locking, but because of their complexity, they would slow down the system if you used them for all internal needs. If you examine locks using the sp_lock system stored procedure or a similar mechanism that gets information from the sys.dm_tran_locks view, you cannot see latches–you see only information about locks.
Another way to look at the difference between locks and latches is that locks ensure the logical consistency of the data and latches ensure the physical consistency. Latching happens when you place a row physically on a page or move data in other ways, such as compressing the space on a page. SQL Server must guarantee that this data movement can happen without interference.
November 1, 2007 at 10:33 am
Pam,
Thank you so much for your reply. That's MUCH better than the explanation of latches I have at hand and once I read what you posted, I actually understood it.
Thanks again! That helps a lot! @=)
November 1, 2007 at 10:53 am
You're welcome. I love that book. Kalen has a blog, too if you're interested:
November 1, 2007 at 11:46 am
And using Safari's search feature I found in the same book:
SQL Server 2000 makes much heavier use of session-owned locks; in SQL Server 2005, transaction_workspace locks are used instead. A workspace holds database locks for sessions that are enlisted into a common environment. Usually, there is one workspace per session, so all DATABASE locks acquired in the session are kept in the same workspace object. In the case of distributed transactions and bound session (discussed later in this chapter), multiple sessions are enlisted into the same workspace, so they share the database locks.
Every process acquires a DATABASE lock with an owner of SHARED_TRANSACTION_WORKSPACE on any database when the process issues the USE command. The exception is any processes that use master or tempdb, in which case no DATABASE lock is taken. That lock isn't released until another USE command is issued or until the process is disconnected. If a process attempts to ALTER, RESTORE, or DROP the database, the DATABASE lock acquired has an owner of EXCLUSIVE_TRANSACTION_WORKSPACE. SHARED_TRANSACTION_WORKSPACE and EXCLUSIVE_TRANSACTION_WORKSPACE locks are maintained by the same workspace and are just two different lists in one workspace. The use of two different owner names is misleading in this case.
From Administrator's Companion I found:
Server 2005 has the following different types of entities that can request a lock from the lock manager:
* CURSOR. A cursor
* EXCLUSIVE_TRANSACTION_WORKSPACE. Exclusive part of the transaction workspace
* TRANSACTION. A transaction
* SESSION. A user session
* SHARED_TRANSACTION_WORKSPACE. Shared part of the transaction workspace
HTH
November 2, 2007 at 10:35 am
The second part of your last post is pretty much what I found in my study book, but it didn't have any accompanying definitions, which is what confused me.
Thanks again for your help, Pam. You really have clarified things for me. I greatly appreciate it.
December 2, 2008 at 5:26 am
Hi
on one of our production servers remained more then a 100 processes that have database locks with object type shared_transaction_workspace , in the details of the processes I see only "set transaction isolation level read committed "
which we don't do explicitely nowhere in the code. All processes are created by the application itself. also there is no explicit use xxxx statement in the code. We use distributed transactions do. Not sure about the cause why they are still there (the processes) . Login time for them is 11.11.2008 and last batch is today. I would kill the processes , but would want to prevent this many processes to appear again if possible.
any ideas?
Thanks
Elod Zsoldos
db developer
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply