Is LOCK IN SHARE MODE a good solution here ?

[phdphd]

Hi all,

 

I am trying to create an event, that does an insert if a condition is met. For this insert, I initially wanted to set some locks. Unfortulately, I get a message saying " LOCK is not allowed in stored procedures". So I redesigned the event as follows. Would this work ? If so, would the LOCK IN SHARE MODE statement apply until after the IF (blablabla > 0) structure has been processed ?

CREATE DEFINER=`root`@`localhost` EVENT `event_name` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-08-10 22:31:56' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN 	
	DECLARE blablabla
	DECLARE blablabla
        DECLARE ...


	SET blablabla
	SET blablabla
        SET ...

	IF (condition_met) THEN


		SELECT SQL_CALC_FOUND_ROWS .....from one table... LOCK IN SHARE MODE;
		SELECT FOUND_ROWS( ) into blablabla;

		IF (blablabla > 0)
			THEN

				INSERT INTO ........another table.....;


		END IF ;



	END IF ;

END

Thanks in advance for your help!

[vinny42]

Locks should apply until you release them, ie: either you explicitely release the locks, or you end the transaction. The lock *should* last until the end of the procedure.

 

That waid, I' curious about what you are doing here, selecting a count to decide what to do, and locking records in a table that you are not updating sounds like you are trying to do something like:

 

INSERT INTO anothertable SELECT * FROM the_table_you_are_lcoking WHERE condition_for_the_locking_query;