Yii Framework Forum: Locking Tables Problem - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

Locking Tables Problem Rate Topic: -----

#1 User is offline   Anand Phulwani 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 05-October 13

Posted 17 October 2013 - 04:01 AM

Hey, I am trying to save an uploaded image on the disk and a record in the database.

1) The image requires the next available id number from the database.
2) The record needs to have image path in the record, which will be generated only image is saved.

So i had no other option than to

1) Lock the tables.
2) Get next available id, pass it to image save.
3) Image save returns the path, which in turn is used to save the record.
4) Unlocking the tables.

   
        public function lockTable($locktype){
            Yii::app()->db->createCommand("LOCK TABLES `".$this->tableName()."` AS `".$this->getTableAlias()."` ".$locktype)->execute();
        }


   
public function save($runValidation=true,$attributes=null)
        {
            /* Lock the tables so that nextavailableid does not changes, No 
             * reading and writing the table is allowed till process is complete.
             */
            $this->lockTable("WRITE");
            if($this->isNewRecord)
                $this->id = $this->getNextAvailableId ();//Generated the next available Id
            if($this->validate())
            {
                if((!$this->uploadFile_createThumb()) || (!parent::save(false)))// If there is any problem in saving this will generate an exception
                {
                    $this->unlockTable();                    
                    $this->deleteFolderContents($this->uploadDirectory);
                    throw new CException('Unable to save the record.');
                    return false;
                }
                return true;
            }
            return false;
        }


        public function getNextAvailableId()
        {
            $criteria=new CDbCriteria;
            $criteria->select='IFNULL(max(id),0) AS maxId';
            $myrow = $this->find($criteria);
            return ($myrow['maxId'])+1;
        }


If i remove the $this->getTableAlias() from the lockTable method i get the following error, which is generated on getNextAvailableId() function 2nd line on $criteria->select.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1100 Table 't' was not locked with LOCK TABLES. The SQL statement executed was: SELECT IFNULL(max(id),0) AS maxId FROM `event` `t` LIMIT 1 


otherwise with the $this->getTableAlias() the following error is produced on save method, when parent::save is called.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1100 Table 'event' was not locked with LOCK TABLES. The SQL statement executed was: INSERT INTO `event` (`start_datetime`, `end_datetime`, `location`, `title`, `description`, `id`, `image`, `IPrecorded`, `create_datetime`, `update_datetime`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, NOW(), NOW()). Bound with :yp0='1970-01-01 00:00:00', :yp1='1970-01-01 00:00:00', :yp2='adasdasd', :yp3='asdasd', :yp4='asdasd', :yp5=5, :yp6='\\event\\5\\640708001382000304_Thumb.jpg', :yp7='192.168.0.102'


Operating System: Windows Xp
Web server: WAMP (Apache)
Browser Type: IE 9.0
Yii version: 1.1
0

#2 User is offline   Anand Phulwani 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 05-October 13

Posted 17 October 2013 - 04:46 AM

Sorted it out by locking the table and table alias both.

Yii::app()->db->createCommand("LOCK TABLES `".$this->tableName()."` ".$locktype.", `".$this->tableName()."` AS `".$this->getTableAlias()."` ".$locktype)->execute();


Thanks neways.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users