Home > Archive > SQL Server Programming > March 2005 > How transacction works?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How transacction works?
|
|
| Kenny M. 2005-03-31, 9:00 am |
| Hi I know this is an basic question
I'm running an Update inside a transacction
The transacction blocks only the the rows I'm updating so others users can
Update different rows? or the whole table is blocked and the User have to
wait for me to finish?
Is the same with Select, Delete and Insert inside a transacction?
Where can I read about this natural DB behavior
thks.
| |
| Wayne Snyder 2005-03-31, 4:03 pm |
| Search on Locking on books on line... but normally an insert, update, or
delete will eventually get an exclusive lock, so no one else can touch the
row in any way until your lock committs or rolls back....
The scope of the lock depends on the optimizers plan, the optimizer could
choose to lock a table row, index range, page, extent or the whole table....
Normally these locks will be at the row or page level to allow max
concurrency...
Hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kenny M." <KennyM@discussions.microsoft.com> wrote in message
news:F721BC94-DA8D-4DA3-93A3-BA84A9150F29@microsoft.com...
> Hi I know this is an basic question
>
> I'm running an Update inside a transacction
>
> The transacction blocks only the the rows I'm updating so others users can
> Update different rows? or the whole table is blocked and the User have to
> wait for me to finish?
>
> Is the same with Select, Delete and Insert inside a transacction?
>
> Where can I read about this natural DB behavior
>
> thks.
>
>
>
| |
| Andrew J. Kelly 2005-03-31, 4:03 pm |
| By default SQL Server will lock at the row level for individual rows or a
small number of affected rows. But you need valid indexes and WHERE clauses
for this to happen. If it is a single insert, update or delete statement
then you don't need to wrap it in a transaction as each statement is Atomic
on it's own.
--
Andrew J. Kelly SQL MVP
"Kenny M." <KennyM@discussions.microsoft.com> wrote in message
news:F721BC94-DA8D-4DA3-93A3-BA84A9150F29@microsoft.com...
> Hi I know this is an basic question
>
> I'm running an Update inside a transacction
>
> The transacction blocks only the the rows I'm updating so others users can
> Update different rows? or the whole table is blocked and the User have to
> wait for me to finish?
>
> Is the same with Select, Delete and Insert inside a transacction?
>
> Where can I read about this natural DB behavior
>
> thks.
>
>
>
| |
| Dan Guzman 2005-03-31, 4:03 pm |
| SQL Server acquires different types of locks depending on available indexes
and data cardinality. If you specify the primary key in your
UPDATE/INSERT/DELETE, chances are that row-level locking will be used and
other users will wait only when accessing uncommitted changes. Conversely,
if a table scan is performed during DML operations, other users will wait
until the transaction is completed.
The main point is that performance and concurrency go hand-in-hand. You can
maximize both with appropriate indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kenny M." <KennyM@discussions.microsoft.com> wrote in message
news:F721BC94-DA8D-4DA3-93A3-BA84A9150F29@microsoft.com...
> Hi I know this is an basic question
>
> I'm running an Update inside a transacction
>
> The transacction blocks only the the rows I'm updating so others users can
> Update different rows? or the whole table is blocked and the User have to
> wait for me to finish?
>
> Is the same with Select, Delete and Insert inside a transacction?
>
> Where can I read about this natural DB behavior
>
> thks.
>
>
>
| |
| Mal .mullerjannie@hotmail.com> 2005-03-31, 4:03 pm |
| Have a look at locks in SQL Books online, locking, transaction isolation
levels. Those words will give more links for you.
HTH
"Kenny M." wrote:
> Hi I know this is an basic question
>
> I'm running an Update inside a transacction
>
> The transacction blocks only the the rows I'm updating so others users can
> Update different rows? or the whole table is blocked and the User have to
> wait for me to finish?
>
> Is the same with Select, Delete and Insert inside a transacction?
>
> Where can I read about this natural DB behavior
>
> thks.
>
>
>
|
|
|
|
|