3 messages in com.mysql.lists.communityRe: Transaction question
FromSent OnAttachments
Szmutku Zoltán01 Jul 2005 02:54 
Jan Wieck01 Jul 2005 04:33 
Arjen Lentz03 Jul 2005 21:20 
Subject:Re: Transaction question
From:Arjen Lentz (arj@mysql.com)
Date:07/03/2005 09:20:17 PM
List:com.mysql.lists.community

Hi,

On Fri, 2005-07-01 at 19:54, Szmutku Zoltán wrote:

A sample for my problem :

CREATE TABLE proba (mezo1 CHAR(10),mezo2 CHAR(10) ) TYPE=BDB INSERT INTO proba VALUES("EGY","EGY") INSERT INTO proba VALUES("KETTO","KETTO")

&& Have a BDB table with 2 rows

After I connect to server from two another client and SET AUTOCOMMIT to 0 both
.

Steps after :

Client 1 : UPDATE proba SET mezo2="HAROM" WHERE mezo1="EGY" && No commit, no rollback

Client2 : SELECT * from proba Where mezo1="KETTO" OR UPDATE proba SET mezo2="HAROM" WHERE mezo1="KETTO"

Both wait for Client1 commit .....

In PostgreSql that simply work. In MySql only work table level locking ? Lack row level locking ?

MySQL offers different concurrency control methods, depending on the table type (storage engine) that you use. BDB (BerkeleyDB) does page-level locking, without multi-versioning.

You probably want to use INNODB tables instead, which has row-level locking and multi-versioning (which makes for non-blocking reads, no lock escalation, etc).

See this and the subsequent articles for more information: http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html

Regards, Arjen.

-- Arjen Lentz, Community Relations Manager MySQL AB, www.mysql.com

The MySQL 5.0 Beta Challenge: http://dev.mysql.com/tech-resources/articles/evaluating-mysql-5.0.html