Hello, I'm having some problems to figure out how I could handle my
problem with innodb.
my table is innodb engine:
CREATE TABLE `videos_innodb` (
`video_id` int(10) unsigned NOT NULL auto_increment,
`client_id` int(10) unsigned default NULL,
`client_id_upload` int(11) NOT NULL default '0',
`state`
enum('GET','QUEUE','AVAILABLE','UPLOAD','ERROR','QUEUE_TRANSCODING')
default 'GET',
`input_file_type_id` int(10) unsigned default NULL,
`output_file_type_id` int(10) unsigned default NULL,
`input_file_name` varchar(150) NOT NULL,
`output_file_name` varchar(150) NOT NULL,
`date_inserted` date default NULL,
`time_inserted` time default NULL,
`date_available` date default '0000-00-00',
`time_available` time default '00:00:00',
`time_start` time NOT NULL,
`time_end` time NOT NULL,
PRIMARY KEY (`video_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12916 DEFAULT CHARSET=latin1
Ok, the column state is really important for my example. I'd like to
start multiple times the same script to convert videos. But I dont want
them to select the same rows for each sessions that select where
state='QUEUE'.
so I tried to use select ... for update with 2 different sessions. I've
set in each of them autocommit=0;
session 1
session 2
set @@autocommit=0;
set @@autocommit=0;
begin;
begin;
select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
=> returns me 10 videos with the state='QUEUE'
select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
=> it's waiting for the first to session to commit, so I cannot get
other videos with the same state!!
commit;
=> I get 10 video_id....
How can I tell mysql to lock only rows that are selected and allow other
sessions to query the table without be locking on the entire table?
Tks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
