Multithreading concurrency same table problem (LI)

In the existing database development process of transaction control, transaction lock, row lock, table lock discovery lacks the necessary methods and means, through the following means can enrich our processing in the development process of the lock problem method. For update and for update of the Enable user to lock the data rows of a specified table or table This function is of great significance in practical application. Especially for multi-user multithreading, this SQL feature is the only best option when you want to get the data first by judging to update the data (which does not allow data to change in the middle) .

In addition, to resolve the deadlock problem caused by the for update, Oracle provides a SELECT ... [For update [of Tab.col]] [nowait] function, which makes it possible to check the requested row, the table resource is available before performing the select...for update, and if it is available, write the lock, or return the Ora-54 error directly. This feature also uses good application value and will play a role in judging the availability of resources in multiple threads.

Table	For Update	For Update of a.ID
A	1. When there is a where condition, the data row specified in the lock condition (row-level blocking); 2. No where condition is, lock table A (table-level blocking).	1. When there is a where condition, the data row specified in the lock condition (row-level blocking); 2. No where condition is, lock table A (table-level blocking).
A, b	Directly block A, B table (table-level blocking)	1. When there is a where condition, block the data row (row-level block) of table A where conditions are met in the Where condition, and b is not locked; 2. No where condition is, lock a table (table level Lock), B table is not locked.

By contrast, it was found that for the single table for update and for update of effects, there was only a difference in multiple table queries, the difference being that the for update of the user was able to lock the data rows of a specified table or table in multiple tables.

Take code as an example: background: There are 4 online task servers that work with data in the same table, and in order to avoid confusing data read and write, use the For Update method to lock.

@SuppressWarnings ("Unchecked")     PublicList<bizexpressdailydo> fetchsomebizexpressdaily (FinalString ServerIP,Final intsome)throwsDataAccessException {return(list<bizexpressdailydo>)NewTransactiontemplate (TransactionManager). Execute (NewTransactioncallback () {@SuppressWarnings ("Rawtypes")             PublicObject dointransaction (transactionstatus status) {//the key to get the lockGetsqlmapclienttemplate (). queryForObject ("Ms-select-action-lock-by-lock-name-for-update", "bizexpress"); List<BizExpressDailyDO> bizexpresses =getsqlmapclienttemplate (). queryForList ( "Ms-find-some-biz-express", integer.valueof (some)); Map param=NewHashMap (); Param.put ("ServerIP", ServerIP); Param.put ("Some", integer.valueof (some)); Getsqlmapclienttemplate (). Update ("Ms-update-some-biz-express", param); returnbizexpresses;    }        }); } above the MS-select-action-lock-by-lock-name-for-UPDATE,<!--lock an ACTION's record--<select id= "Ms-select-action-lock-by-lock-name-for-update" parameterclass= " Java.lang.String "> <![cdata[SELECT* from Action_lock WHERE lock_name =#value # for UPDATE]]> </select>

Pass this code to lock the table so that other threads will be waiting when executed here, until the table lock is released, which restricts other threads from accessing the biz_express_daily table to execute the following SQL statement.

<!--pick out some records to wait for updates -    <SelectID= "Ms-find-some-biz-express"Resultmap= "Rm-biz-express-daily">        <! [Cdata[SELECT * from biz_express_daily WHERE server_ip = ' 0.0.0.0 ' and ROWNUM < #some #]]>    </Select>  <!--update Some records of the server_ip -    <UpdateID= "Ms-update-some-biz-express">        <! [Cdata[UPDATE biz_express_daily SET server_ip = #serverIp #, gmt_modified = sysdate WHERE server_ip = ' 0.0.0.0 ' an D ROWNUM < #some #]]>    </Update>

So the problem of multi-threaded concurrent database table is solved effectively.

Multithreading concurrency same table problem (LI)