Bug #18269 update query's problem (about atomical property)
Submitted: 16 Mar 2006 7:01 Modified: 26 Apr 2006 4:33
Reporter: park ji hyeon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.12-log OS:Linux (Red Hat Enterprise Linux ES)
Assigned to: CPU Architecture:Any

[16 Mar 2006 7:01] park ji hyeon
Description:
when update query excuted "at the same time",

ex) 

update table set a=5 where id='user' and a=4;
update table set a=7 where id='user' and a=4;

 

How do you think about them? Will both of them write successfully? or Will first one excute write only?

 

surprisingly.... both are writed. I can confirm through binlog file.

 

1. update table set a=5 where id='user' and a=4; (writing succeed) ==> result : a=5

2. update table set a=7 where id='user' and a=4; (fail : because a is not 4 any more) -> so this is not able to recorded as update query in binlog file.

 

2 CPU (using Hyper threading)

OS : Red Hat Enterprise Linux ES 

mysql version :  4.1.12-log 

 

It shows that update query's operation principle doesn't include concept of atomic and isolation properties. Is it true?

How to repeat:
update query excuted "at the same time",

ex) 

update table set a=5 where id='user' and a=4;
update table set a=7 where id='user' and a=4;

confirm : use binlog

Suggested fix:
use serializable schedule for isolation.
[16 Mar 2006 8:35] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW VARIABLES command from your server. I wonder what is the value of innodb_locks_unsafe_for_binlog, for example.

A more precise description of a test case, including CREATE TABLE and, maybe, binlog, will be also useful.
[17 Mar 2006 0:18] park ji hyeon
+ Show variables Results
-------------------------------------------
Variable_name	Value
back_log	50
basedir	/usr/
bdb_cache_size	8388600
bdb_home	/var/lib/mysql/
bdb_log_buffer_size	262144
bdb_logdir	
bdb_max_lock	10000
bdb_shared_data	OFF
bdb_tmpdir	/tmp/
binlog_cache_size	32768
bulk_insert_buffer_size	8388608
character_set_client	euckr
character_set_connection	euckr
character_set_database	euckr
character_set_results	euckr
character_set_server	euckr
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/
collation_connection	euckr_korean_ci
collation_database	euckr_korean_ci
collation_server	euckr_korean_ci
concurrent_insert	ON
connect_timeout	5
datadir	/var/lib/mysql/
date_format	%Y-%m-%d
datetime_format	%Y-%m-%d %H:%i:%s
default_week_format	0
delay_key_write	ON
delayed_insert_limit	100
delayed_insert_timeout	300
delayed_queue_size	1000000
expire_logs_days	0
flush	OFF
flush_time	0
ft_boolean_syntax	+ -><()~*:""&|
ft_max_word_len	84
ft_min_word_len	4
ft_query_expansion_limit	20
ft_stopword_file	(built-in)
group_concat_max_len	1024
have_archive	NO
have_bdb	YES
have_blackhole_engine	NO
have_compress	YES
have_crypt	YES
have_csv	NO
have_example_engine	NO
have_geometry	YES
have_innodb	YES
have_isam	YES
have_ndbcluster	NO
have_openssl	YES
have_query_cache	YES
have_raid	NO
have_rtree_keys	YES
have_symlink	YES
init_connect	set names euckr
init_file	
init_slave	
innodb_additional_mem_pool_size	1048576
innodb_autoextend_increment	8
innodb_buffer_pool_awe_mem_mb	0
innodb_buffer_pool_size	8388608
innodb_data_file_path	ibdata1:10M:autoextend
innodb_data_home_dir	
innodb_fast_shutdown	ON
innodb_file_io_threads	4
innodb_file_per_table	OFF
innodb_flush_log_at_trx_commit	1
innodb_flush_method	
innodb_force_recovery	0
innodb_lock_wait_timeout	50
innodb_locks_unsafe_for_binlog	OFF
innodb_log_arch_dir	
innodb_log_archive	OFF
innodb_log_buffer_size	1048576
innodb_log_file_size	5242880
innodb_log_files_in_group	2
innodb_log_group_home_dir	./
innodb_max_dirty_pages_pct	90
innodb_max_purge_lag	0
innodb_mirrored_log_groups	1
innodb_open_files	300
innodb_table_locks	ON
innodb_thread_concurrency	8
interactive_timeout	28800
join_buffer_size	131072
key_buffer_size	402653184
key_cache_age_threshold	300
key_cache_block_size	1024
key_cache_division_limit	100
language	/usr/share/mysql/english/
large_files_support	ON
license	GPL
local_infile	ON
locked_in_memory	OFF
log	OFF
log_bin	ON
log_error	
log_slave_updates	OFF
log_slow_queries	ON
log_update	OFF
log_warnings	1
long_query_time	10
low_priority_updates	OFF
lower_case_file_system	OFF
lower_case_table_names	0
max_allowed_packet	1047552
max_binlog_cache_size	18446744073709551615
max_binlog_size	1073741824
max_connect_errors	1000
max_connections	500
max_delayed_threads	20
max_error_count	64
max_heap_table_size	104856576
max_insert_delayed_threads	20
max_join_size	18446744073709551615
max_length_for_sort_data	1024
max_relay_log_size	0
max_seeks_for_key	18446744073709551615
max_sort_length	1024
max_tmp_tables	32
max_user_connections	0
max_write_lock_count	18446744073709551615
myisam_data_pointer_size	4
myisam_max_extra_sort_file_size	2147483648
myisam_max_sort_file_size	9223372036854775807
myisam_recover_options	OFF
myisam_repair_threads	1
myisam_sort_buffer_size	67108864
net_buffer_length	16384
net_read_timeout	30
net_retry_count	10
net_write_timeout	60
new	OFF
old_passwords	OFF
open_files_limit	2510
pid_file	/var/run/mysqld/mysqld.pid
port	3306
preload_buffer_size	32768
protocol_version	10
query_alloc_block_size	8192
query_cache_limit	1048576
query_cache_min_res_unit	4096
query_cache_size	67108864
query_cache_type	ON
query_cache_wlock_invalidate	OFF
query_prealloc_size	8192
range_alloc_block_size	2048
read_buffer_size	2093056
read_only	OFF
read_rnd_buffer_size	262144
relay_log_purge	ON
relay_log_space_limit	0
rpl_recovery_rank	0
secure_auth	OFF
server_id	1
skip_external_locking	ON
skip_networking	OFF
skip_show_database	OFF
slave_net_timeout	3600
slave_transaction_retries	0
slow_launch_time	2
socket	/var/lib/mysql/mysql.sock
sort_buffer_size	2097144
sql_mode	
storage_engine	MyISAM
sql_notes	ON
sql_warnings	ON
sync_binlog	0
sync_replication	0
sync_replication_slave_id	0
sync_replication_timeout	0
sync_frm	ON
system_time_zone	KST
table_cache	512
table_type	MyISAM
thread_cache_size	8
thread_stack	196608
time_format	%H:%i:%s
time_zone	SYSTEM
tmp_table_size	33554432
tmpdir	
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
tx_isolation	REPEATABLE-READ
version	4.1.12-log
version_bdb	Sleepycat Software: Berkeley DB 4.1.24: (May 13, 2005)
version_comment	Source distribution
version_compile_machine	x86_64
version_compile_os	redhat-linux-gnu
wait_timeout	28800
------------------------------------------------------

table name : let "table" at my option.

+ Source code

//when user buys item.
$balance = mysql_fetch_object(mysql_query("SELECT money, point, mileage FROM table WHERE user_id = '$user_id'",$db_conn));  //check the balance in an account
$amount = $balance->money + $balance->point + $balance->mileage;  //sum 
include "change.inc";	//compute money, point, mileage -> afterMoney, afterPoint, afterMileage
if($balance >= $priceOfItem) {
    $deductMoney = mysql_query("UPDATE table SET money = afterMoney , point = afterPoint , mileage = afterMileage WHERE user_id = 'user_id' and money = $balance->money and point = $balance->point and mileage = $balance->mileage",$db_conn);
}

+binlog
# at 42671218
#060316  2:40:31 server id 1  log_pos 42671218  Query   thread_id=220925419     exec_time=0     error_code=0
SET TIMESTAMP=1142444431;
UPDATE table SET money = 13000, point = 0, mileage = 0 WHERE user_id = 'test_id' and money = 24000 and point = 0 and mileage = 0
nd money4 = '0';
# at 42671404
#060316  2:40:31 server id 1  log_pos 42671404  Query   thread_id=220925420     exec_time=0     error_code=0
SET TIMESTAMP=1142444431;
UPDATE table SET money = 15000, point = 0, mileage = 0 WHERE user_id = 'test_id' and money = 24000 and point = 0 and mileage = 0
nd money4 = '0';

+ schema
CREATE TABLE `table` (
  `id` int(11) NOT NULL default '0',
  `user_id` varchar(30) default NULL,
  `money` int(11) unsigned NOT NULL default '0',
  `point` int(11) unsigned NOT NULL default '0',
  `mileage` int(11) unsigned NOT NULL default '0',
  `tran_counter` mediumint(8) unsigned default '0',
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=euckr

+ description
As you show, I don't use Innodb and transaction.
I want to comment about Update query's execution only.
at binlog :
UPDATE table SET money = 13000, point = 0, mileage = 0 WHERE user_id = 'test_id' and money = 24000 and point = 0 and mileage = 0
nd money4 = '0';
UPDATE table SET money = 15000, point = 0, mileage = 0 WHERE user_id = 'test_id' and money = 24000 and point = 0 and mileage = 0
nd money4 = '0';

Is it possible?
binlog records queries that associated writing & succeeded(finished) work.

According to result, I think that update execute in non-isolation.
when same query's execution happen concurrently, 
If second query's "where" next phrase (read working) execute before first query's writing completed....It's possible.

So I test that 'table lock' allocate update query's before and after.
but result is same.

Should I use transaction(on Innodb engine)?

Please let me know, your opinion & result that try to find the cause of it.
[24 Apr 2006 15:23] Valeriy Kravchuk
You need to use InnoDB tables to get ACID properties of transactions, in general. 

As for your updates of MyISAM table, they are atomic, but not concurrent anyway - they are serialized (by means of table-level locks) and executed one after the other, just as shown in the binary log.

I see no bugs in this report.
[26 Apr 2006 4:33] park ji hyeon
Thank you for your comment.

As you said, If they are serialized I think that it would not be happened.

Even though MyIsam doesn't server concurrency control if it has serializability, there's no problem. It just makes decreasing of speed.

Problem is isolation level. It seems like "uncommitted read"
[28 May 2006 9:04] Sergei Golubchik
You seem to believe that if update did not match/modify any rows it should not be written to binlog. This is wrong, a query is written to binlog even if it has not done anything.
Try, for example, to create an empty table, and run an update on it - it will be written to binlog.