Insert

[victor regs]

Why is that the one row insert can cause an exclusive table lock?
Pls explain.

Thanks in advance.
Vic

[Mark S.]

Victor,

An exclusive lock is used for write transactions (INSERT, UPDATE, or DELETE). Other transactions must wait for the xclusive lock to be released before they can read or write information.

Why are Exclusive locks necessary? Well, perhaps an example involving a checking and savings account might be appropriate here. (Thi relates more to an UPDATE but I think you'll get the idea) Suppose you decide to transfer $100 from checking to savings (big spender). Your bank decides to run a report that shows your combined balance for checking and savings. What happens if the report is run while the transfer is in progress if no locks were used? The total could be wrong...here's why.

Step1 - Update Checking by subtracting 100 from balance
Step2 - Update Savings by adding 100 to balance

Checking Savings Combined Balance
Before transfer 100 100 200
Step 1 0 100 100
Step 2 0 200 200
After Transaction 0 200 200

So as you can see if you weren't kept from accessing this information there is a strong possibility you would have an incorrect total and some fairly grumpy bank customers.

When Pending changes can be read by a transaction, it is known as a DIRTY READ. Exclusive locks prevent dirty reads.

Regards,

Mark S.

------------
victor regs at 10/12/99 8:21:52 AM

Why is that the one row insert can cause an exclusive table lock?
Pls explain.

Thanks in advance.
Vic

[Gregory]

look for "lock escalation":
When a query requests rows from a table, SQL Server automatically generates page-level locks. However, if the query requests a large percentage of the table's rows, then SQL Server escalates the locking from page level to table level.
makes table scans and operations against a large results set more efficient because it reduces locking overhead.

from Books online

LE threshold maximum*
Determines the maximum number of page locks to hold before escalating to a table lock. If the number of page locks is greater than the escalation maximum, a table lock will occur regardless of whether or not the LE threshold percent has been exceeded. The default is 200.

LE threshold minimum*
Determines the minimum number of page locks required before escalating to a table lock. A table lock will occur only if this minimum is reached when the LE threshold percent is exceeded. LE threshold minimum prevents the server from escalating to a table lock for small tables where the LE threshold percentage is reached quickly. The default is 20.

LE threshold percent*
Specifies the percentage of page locks needed on a table before a table lock is requested. The default (0) causes a table lock to occur only when the LE threshold maximum has been reached.

Note To override these values for a given request, see the optimizer_hints option with the SELECT statement. The HOLDLOCK table lock options include UPDLOCK, TABLOCK, TABLOCKX, and PAGLOCK.


------------
Mark S. at 10/12/99 4:04:32 PM

Victor,

An exclusive lock is used for write transactions (INSERT, UPDATE, or DELETE). Other transactions must wait for the xclusive lock to be released before they can read or write information.

Why are Exclusive locks necessary? Well, perhaps an example involving a checking and savings account might be appropriate here. (Thi relates more to an UPDATE but I think you'll get the idea) Suppose you decide to transfer $100 from checking to savings (big spender). Your bank decides to run a report that shows your combined balance for checking and savings. What happens if the report is run while the transfer is in progress if no locks were used? The total could be wrong...here's why.

Step1 - Update Checking by subtracting 100 from balance
Step2 - Update Savings by adding 100 to balance

Checking Savings Combined Balance
Before transfer 100 100 200
Step 1 0 100 100
Step 2 0 200 200
After Transaction 0 200 200

So as you can see if you weren't kept from accessing this information there is a strong possibility you would have an incorrect total and some fairly grumpy bank customers.

When Pending changes can be read by a transaction, it is known as a DIRTY READ. Exclusive locks prevent dirty reads.

Regards,

Mark S.

------------
victor regs at 10/12/99 8:21:52 AM

Why is that the one row insert can cause an exclusive table lock?
Pls explain.

Thanks in advance.
Vic

[victor regs]

Thanks Guys for the response, but what i don't understand is why
it is escalating to an EXclusive TABLE lock when I'm just inserting
a single row? A single row insert can only affect one to two pages(page split), so an exclusive page lock would be more appropriate because
it doesn't affect majority of the data. It is possible to have a table
lock if the table consists of only few records, but not in my case which
has thousands of records.

Thanks.

Dion


------------
Gregory at 10/13/99 10:18:46 AM

look for "lock escalation":
When a query requests rows from a table, SQL Server automatically generates page-level locks. However, if the query requests a large percentage of the table's rows, then SQL Server escalates the locking from page level to table level.
makes table scans and operations against a large results set more efficient because it reduces locking overhead.

from Books online

LE threshold maximum*
Determines the maximum number of page locks to hold before escalating to a table lock. If the number of page locks is greater than the escalation maximum, a table lock will occur regardless of whether or not the LE threshold percent has been exceeded. The default is 200.

LE threshold minimum*
Determines the minimum number of page locks required before escalating to a table lock. A table lock will occur only if this minimum is reached when the LE threshold percent is exceeded. LE threshold minimum prevents the server from escalating to a table lock for small tables where the LE threshold percentage is reached quickly. The default is 20.

LE threshold percent*
Specifies the percentage of page locks needed on a table before a table lock is requested. The default (0) causes a table lock to occur only when the LE threshold maximum has been reached.

Note To override these values for a given request, see the optimizer_hints option with the SELECT statement. The HOLDLOCK table lock options include UPDLOCK, TABLOCK, TABLOCKX, and PAGLOCK.


------------
Mark S. at 10/12/99 4:04:32 PM

Victor,

An exclusive lock is used for write transactions (INSERT, UPDATE, or DELETE). Other transactions must wait for the xclusive lock to be released before they can read or write information.

Why are Exclusive locks necessary? Well, perhaps an example involving a checking and savings account might be appropriate here. (Thi relates more to an UPDATE but I think you'll get the idea) Suppose you decide to transfer $100 from checking to savings (big spender). Your bank decides to run a report that shows your combined balance for checking and savings. What happens if the report is run while the transfer is in progress if no locks were used? The total could be wrong...here's why.

Step1 - Update Checking by subtracting 100 from balance
Step2 - Update Savings by adding 100 to balance

Checking Savings Combined Balance
Before transfer 100 100 200
Step 1 0 100 100
Step 2 0 200 200
After Transaction 0 200 200

So as you can see if you weren't kept from accessing this information there is a strong possibility you would have an incorrect total and some fairly grumpy bank customers.

When Pending changes can be read by a transaction, it is known as a DIRTY READ. Exclusive locks prevent dirty reads.

Regards,

Mark S.

------------
victor regs at 10/12/99 8:21:52 AM

Why is that the one row insert can cause an exclusive table lock?
Pls explain.

Thanks in advance.
Vic