SEO

Hi All,

How transactions and locking are handled in MYSQL?

Is it a part of configuration? Or a query (lock tables, Unlock tables)
for each set of queries?



Regards,

Ravi K






The information contained in this electronic message and any attachments tothis message are intended for the exclusive use of the addressee(s) andmay contain proprietary, confidential or privileged information. If youare not the intended recipient, you should not disseminate, distribute orcopy this e-mail. Please notify the sender immediately and destroy allcopies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipientshould check this email and any attachments for the presence of viruses.The company accepts no liability for any damage caused by any virustransmitted by this email.

www.wipro.com

It only works with engines that support transactions like innodb and
solid, i strongly sugget to read these links from the manual.

http://dev.mysql.com/doc/refman/5.0/...nsactions.html

http://dev.mysql.com/doc/refman/5.0/...ion-model.html

Carlos


On 9/25/06, ravi.karatagi@wipro.com <ravi.karatagi@wipro.com> wrote:
>
> Hi All,
>
> How transactions and locking are handled in MYSQL?
>
> Is it a part of configuration? Or a query (lock tables, Unlock tables)
> for each set of queries?
>
>
>
> Regards,
>
> Ravi K
>
>
>
>
>
>
> The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
>
> www.wipro.com
>

Hi,

All locking in *MySQL* is deadlock-free. This is managed by always
requesting all needed locks at once at the beginning of a query and always
locking the tables in the same order.

The --external-locking and --skip-external-locking options explicitly enable
and disable external locking.

The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you
can use them even if external locking is disabled.

A call to LOCK TABLES tries to lock any tables you list so that the current
thread can work with it without interference. A call to UNLOCK TABLES
releases any locks that this thread holds.There are two main types of locks:
read and write.

When a lock is released, the lock is made available to the threads in the
write lock queue, then to the threads in the read lock queue.

External locking is a part of configuration and Internal locking is a part
of query.

Thanks
ViSolve DB Team
----- Original Message -----
From: <ravi.karatagi@wipro.com>
To: <mysql@lists.mysql.com>
Sent: Monday, September 25, 2006 11:42 AM
Subject: Transactions in MySQL.



Hi All,

How transactions and locking are handled in MYSQL?

Is it a part of configuration? Or a query (lock tables, Unlock tables)
for each set of queries?



Regards,

Ravi K






The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not
the intended recipient, you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately and destroy all copies of this
message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email.

www.wipro.com

Visolve DB Team wrote:
> Hi,
>
> All locking in *MySQL* is deadlock-free. This is managed by always
> requesting all needed locks at once at the beginning of a query and always
> locking the tables in the same order.
>

Not true. You have to design the applications properly to prevent
potential deadlocks. It is quite possible to get deadlocks if two
applications are trying to update the same (multiple) rows at the same time.

> The --external-locking and --skip-external-locking options explicitly
> enable
> and disable external locking.
>
> The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you
> can use them even if external locking is disabled.
>

And kill database concurrency. This is definitely a BAD IDEA. It will
work OK for a lightly used database. But as the database gets busier,
this will become more of a problem, until the database is almost unusable.

> A call to LOCK TABLES tries to lock any tables you list so that the current
> thread can work with it without interference. A call to UNLOCK TABLES
> releases any locks that this thread holds.There are two main types of
> locks:
> read and write.
>

Yep, and it means no one else can update that table - even if they are
updating a different row in the table.

> When a lock is released, the lock is made available to the threads in the
> write lock queue, then to the threads in the read lock queue.
>

And meanwhile all those threads are waiting, even if they aren't
accessing rows which are being updated.

> External locking is a part of configuration and Internal locking is a part
> of query.
>
> Thanks
> ViSolve DB Team
>

P.S. Please don't top post.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================